Using a function to count cells based on multiple criteria

R

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

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

Or, use cells to hold the ctieria:

E1 = apples
F1 = red

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top