is there a way to have 2 criterias for COUNTIF?

[doyree]

Please help me with following!!

A B
APPLE FRUIT
PEACH FRUIT
APPLE FRUIT
APPLE NO FRUIT
PEACH NO FRUIT

I have above list and i would like to get a cell count for
"APPLE" WITH "FRUIT"
"APPLE" WITH "NO FRUIT"
"PEACH" WITH "FRUIT"
"PEACH" WITH "NO FRUIT"

i prefer not using pivot or dcounta

thank you!!!

 

[PCLIVE]

=SUMPRODUCT(--(A1:A5="APPLE"),--(B1:B5="FRUIT"))

Adjust for other criteria.

HTH,
Paul

 

[Tyro]

Try, for example =SUMPRODUCT(--(A1:A10="Apple"),--(B1:B10="Fruit")) Answer
2. Adjust formula for other combinations.

Tyro

 

[Laura Cook]

Try the following:

=SUMPRODUCT((A2:A6="Apple")*(B2:B6="Fruit"))

Laura

 

[doyree]

THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!!


what's "--" in that formula mean?
thanks

 

[doyree]

Sorry to bother you but is there way to put a cell in place of "apple"?
say... C4 in place of "apple"?
thank you!!

 

[Tyro]

The "--" coerces the conversion of the logical values TRUE and FALSE to be
1 and 0. One minus makes TRUE = -1 and FALSE = 0. The second minus reverses
the signs, so TRUE becomes 1 and FALSE becomes 0. Then the numbers are
multiplied and summed. For every "Apple" in A1:A5 there will be a 1 and for
every "Fruit" in B1:B5 there will be a 1. So apple and fruit in the same row
results in 1 * 1, and all other combinations result in 0.

Tyro

 

[Tyro]

Yep. Put C4 in place of apple.

Tyro

Sorry to bother you but is there way to put a cell in place of "apple"?
say... C4 in place of "apple"?
thank you!!