SumIf Problem

  • Thread starter Thread starter Brian Matlack
  • Start date Start date
B

Brian Matlack

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

I'm probably overthinking this but I cannot make it work. Here is wha
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
 
I would add a column that concatenates column A and column E. Say we insert
that column between F & G, so that it becomes the new G.

So in G2 we have =A2&E2
and in I2 we have
=SUMIF(G4:G58521,H2&"1",C4:C58521)
 
Brian said:
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!
Click to expand...

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)
 
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,"=1",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
 
Hi Brian,
when you say...

"if adjacent cell value (column G part number) is found in column A
(list of all part numbers)"
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!!
 
Hi Brian,

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

Ken Johnson
 
Back
Top