SumIf Problem

[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

 

[Guest]

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)

 

[daddylonglegs]

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!

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)

 

[Jim May]

Use in cell H4:
=SUMPRODUCT((A4:A15=G4:G15) * (MONTH(E4:E15)=1) * (C4:C15))




"Brian Matlack" <[email protected]>
wrote in message
news:[email protected]...

 

[blackbox via OfficeKB.com]

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

 

[Ken Johnson]

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

 

[Brian Matlack]

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!!

 

[Ken Johnson]

Hi Brian,

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

Ken Johnson