Advanced Criteria for SUMIF

[Roberto Villa Real]

I need to sum some values in Column C, but with 2 criterias (one criteria for
column A and another criteria for column B).

Exemple:
I need the SUM of values in column C, only if Column A="x" AND Column
B="09/03/05"

A______B__________C____

x____09/03/05_____2.99(*)
j____09/03/05_____10.34
y____09/03/05_____1.78
h____12/03/05_____9.21
x____09/03/05_____5.6(*)
x____10/04/05_____22.5
a____05/05/05_____50.02

Total: (2.99 + 5.6) = 8.59

Hope someone helps me!
Thanks a lot!

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A1000="x"),--(B2:B1000=DATE(2005,9,5)),C2:C1000)

look in help for DATE, 09/03/05 can be either March 9th 2005 or September
3rd 2005 depending on if you use UK
d/m/y or US m/d/y/ I used US in my formula. You can also write it like this


=SUMPRODUCT(--(A2:A1000="x"),--(B2:B1000=--"2005-09-05"),C2:C1000)

still interpreting your example as US date


--


Regards,


Peo Sjoblom

 

[Pete_UK]

Try this:

=SUMPRODUCT((A1:A10="x")*(B1:B10=--"09/03/05")*(C1:C10))

Hope this helps.

Pete

 

[Roger Govier]

Hi Roberto

=SUMPRODUCT(--($A$1:$A$100="x"),--($B$1:$B$100=Date(2005,9,3)),--($C$1:$C$100))

I am assuming your date is 9th March 2005. If not change the date value to
match the date you mean.

It might be better to put you "x" in say cell E1 and your date in cell F1
(09/03/05) then substitute these cell references in the formula.
Changing the values in E1 and F1 will allow you to use the formula for other
calculations
=SUMPRODUCT(--($A$1:$A$100=$E$1),--($B$1:$B$100=$F$1),--($C$1:$C$100))

 

[Hambo_NZ]

maaate! I'm glad you asked that question, cos the answers helped me save
heaps of time and headaches. the following formula worked for me:

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"),C1:C100)

Excellent forum, it inspired me to join, thanks to all!!