more than 1 criteria

[Chiam]

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!

 

[T. Valko]

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)

 

[Rick Rothstein \(MVP - VB\)]

Try this...

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

Rick