software

Hi!
Can I put a dual criteria in a SumIf Function? I want to sum column C
(Reciepts) if adjacent cell value (column G part number) is found in
column A (list of all part numbers) and if column E = 1 (month of
January).

I'm probably overthinking this but I cannot make it work. Here is what
I have that doesnot work. This formula is in column H.

=SUMIF(A4:A58521,AND(G2,E2=1),C4:C58521)

Thanks for any help or suggestions!--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: www.excelforum.com/member.php...foamp;userid=3508
View this thread: www.excelforum.com/showthread...hreadid=532801I would add a column that concatenates column A and column E. Say we insert
that column between F amp; G, so that it becomes the new G.

So in G2 we have =A2amp;E2
and in I2 we have
=SUMIF(G4:G58521,H2amp;quot;1quot;,C4:C58521)quot;Brian Matlackquot; wrote:

gt;
gt; Hi!
gt; Can I put a dual criteria in a SumIf Function? I want to sum column C
gt; (Reciepts) if adjacent cell value (column G part number) is found in
gt; column A (list of all part numbers) and if column E = 1 (month of
gt; January).
gt;
gt; I'm probably overthinking this but I cannot make it work. Here is what
gt; I have that doesnot work. This formula is in column H.
gt;
gt; =SUMIF(A4:A58521,AND(G2,E2=1),C4:C58521)
gt;
gt; Thanks for any help or suggestions!
gt;
gt;
gt; --
gt; Brian Matlack
gt; ------------------------------------------------------------------------
gt; Brian Matlack's Profile: www.excelforum.com/member.php...foamp;userid=3508
gt; View this thread: www.excelforum.com/showthread...hreadid=532801
gt;
gt;


Brian Matlack Wrote:
gt; Hi!
gt; Can I put a dual criteria in a SumIf Function? I want to sum column C
gt; (Reciepts) if adjacent cell value (column G part number) is found in
gt; column A (list of all part numbers) and if column E = 1 (month of
gt; January).
gt;
gt; I'm probably overthinking this but I cannot make it work. Here is what
gt; I have that doesnot work. This formula is in column H.
gt;
gt; =SUMIF(A4:A58521,AND(G2,E2=1),C4:C58521)
gt;
gt; Thanks for any help or suggestions!

For more than one criterion SUMPRODUCT is usually more appropriate...

SUMPRODUCT(--(A$4:A$58521=G2),--(E$4:E$58521=1),C$4:C$58521)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=532801Use in cell H4:
=SUMPRODUCT((A4:A15=G4:G15) * (MONTH(E4:E15)=1) * (C4:C15))

quot;Brian Matlackquot; gt;
wrote in message
news:Brian.Matlack.2683yn_1144966202.3654@excelfor um-nospam.com...
gt;
gt; Hi!
gt; Can I put a dual criteria in a SumIf Function? I want to sum column C
gt; (Reciepts) if adjacent cell value (column G part number) is found in
gt; column A (list of all part numbers) and if column E = 1 (month of
gt; January).
gt;
gt; I'm probably overthinking this but I cannot make it work. Here is what
gt; I have that doesnot work. This formula is in column H.
gt;
gt; =SUMIF(A4:A58521,AND(G2,E2=1),C4:C58521)
gt;
gt; Thanks for any help or suggestions!
gt;
gt;
gt; --
gt; Brian Matlack
gt; ------------------------------------------------------------------------
gt; Brian Matlack's Profile:
gt; www.excelforum.com/member.php...foamp;userid=3508
gt; View this thread: www.excelforum.com/showthread...hreadid=532801
gt;
Is column E (month) just a number? 1 for Jan, or do you have an actual date?
01/15/2006
The date 01/15/2006 has a number value of 38732.

Just to make sure I understand what you want,
If E2 and G2 both equal 1, you want to sum column C rows 4 to 58521 for
corrisponding cells in column A that have a value of 1.
Is this correct?

What's in C1 - C3? if they are not numbers than this may work.
=IF(AND(E2=1,G2=1),SUMIF(A1:A58521,quot;=1quot;,C1:C58521) )

with the criteria array in the SUMIF statement starting at row 1 it will sum
column C starting at row 1 as well.

good luck

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200604/1

Hi Brian,
when you say...

quot;if adjacent cell value (column G part number) is found in column A
(list of all part numbers)quot;
does this mean 'found anywhere in column A', or does it mean 'found in
the same row of column A' ?

Because you have called column A a 'List of all part numbers' I have
assumed you mean 'found anywhere in column A'.

If my interpretation of your question is correct then I think VLOOKUP
is going to have to be a part of the solution. I don't think that
SUMPRODUCT can work like VLOOKUP ((but of course I could be wrong,
amazing things have been achieved using SUMPRODUCT).

If you add the following formula to a spare column, say column H...

=ISNUMBER(VLOOKUP(G4,A$4:A$58521,1,FALSE))
then fill down to row 58521

then use...

=SUMPRODUCT((E4:E58521=1)*H4:H58521*C4:C58521) in your Sum cell

you should get the sum you are after if my interpretation of your
question is correct.

Ken Johnson
Thanks to all, for the great ideas! I was able to make the concantinate
idea, from GoBobbyGo, work well. I will save the others for future
reference and no doubt get use out of them as well.

Thanks again!!--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: www.excelforum.com/member.php...foamp;userid=3508
View this thread: www.excelforum.com/showthread...hreadid=532801Hi Brian,

Thanks for the feedback.
It's nice knowing you've had success.

Ken Johnson