Advanced Criteria for SUMIF

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!

=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

"Roberto Villa Real" wrote in
message ...
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!

Try this:

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

Hope this helps.

Pete

On Aug 14, 4:18*pm, Roberto Villa Real
wrote:
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!

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

--
Regards
Roger Govier

"Roberto Villa Real" wrote in
message ...
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!

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



"Roberto Villa Real" wrote:

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!