Using a function to count cells based on multiple criteria
 

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

Using a function to count cells based on multiple criteria
 

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"),--(D2:D10="red"))

Or, use cells to hold the ctieria:

E1 = apples
F1 = red

=SUMPRODUCT(--(A2:A10=E1),--(D2:D10=F1))


--
Biff
Microsoft Excel MVP


"Rae" wrote in message
...
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