Hi all,

Eg below
A B C
1 apples black $500
2 apples pink $200
3 orange black $100
4 apples black $250
5 pear yellow $300

If I want to add the corresponding values in column C for those apples that
are black (note that there are pink and black apples), what is the formula I
should use? Effectively, this means 2 criterias (column A and B) have to be
met before Excel will add their values (in column C) in the cell that I keyed
in the formula.

Thanks!

Try this:

=SUMPRODUCT(--(A1:A5="apples"),--(B1:B5="black"),C1:C5)

Better to use cells to hold the criteria:

E1 = apples
F1 = black

=SUMPRODUCT(--(A1:A5=E1),--(B1:B5=F1),C1:C5)



--
Biff
Microsoft Excel MVP


"Chiam" wrote in message
...
Hi all,

Eg below
A B C
1 apples black $500
2 apples pink $200
3 orange black $100
4 apples black $250
5 pear yellow $300

If I want to add the corresponding values in column C for those apples
that
are black (note that there are pink and black apples), what is the formula
I
should use? Effectively, this means 2 criterias (column A and B) have to
be
met before Excel will add their values (in column C) in the cell that I
keyed
in the formula.

Thanks!

Try this...

=SUMPRODUCT((A1:A1000="apples")*(B1:B1000="black") *C1:C1000)

Rick


"Chiam" wrote in message
...
Hi all,

Eg below
A B C
1 apples black $500
2 apples pink $200
3 orange black $100
4 apples black $250
5 pear yellow $300

If I want to add the corresponding values in column C for those apples
that
are black (note that there are pink and black apples), what is the formula
I
should use? Effectively, this means 2 criterias (column A and B) have to
be
met before Excel will add their values (in column C) in the cell that I
keyed
in the formula.

Thanks!