Using a function to count cells based on multiple criteria

[Rae]

I am trying to count the values of a cell range based on two different cell
ranges containing certain data.

For example:
cell range a2 - a4 contains apples
cell range a5 - a8 contains pears
cell range a9 - a10 contains apples
and cell range d2 - d7 contains red
and cell range d8 - d10 contains green

count cell range d2-d10 that has a value of red AND cell range a2-a10 has a
value of apples and return a value of 5 (5 records/rows have a value of
apples and red)

Does this make sense?

Thanks

 

[T. Valko]

return a value of 5 (5 records/rows have a value of apples and red)

Does this make sense?

No. The correct result would be 3. Here's how you do it:

=SUMPRODUCT(--(A2:A10="apples"),--(D210="red"))

Or, use cells to hold the ctieria:

E1 = apples
F1 = red

=SUMPRODUCT(--(A2:A10=E1),--(D210=F1))