Excel - Counting unique values that meet multiple criteria

Posted by wotaskd on Stack Overflow See other posts from Stack Overflow or by wotaskd
Published on 2011-02-23T15:21:42Z Indexed on 2011/02/23 15:25 UTC
Read the original article Hit count: 179

Filed under:

I'm trying to use a function to count the number of unique cells in a spreadsheet that, at the same time, meet multiple criteria.

Given the following example:

A        B       C
QUANT    STORE#  PRODUCT
1        75012   banana
5                orange
6        56089   orange
3        89247   orange
7        45321   orange
2                apple
4        45321   apple

In the example above, I need to know how many unique stores with a valid STORE# have received oranges OR apples. In the case above, the result should be 3 (stores 56089, 89247 and 45321).

This is how I started to try solving the problem:

=SUM(IF(FREQUENCY(B2:B9,B2:B9)>0,1))

The above formula will yield the number of unique stores with a valid store#, but not just the ones that have received oranges or bananas. How can I add that extra criteria?

© Stack Overflow or respective owner

Related posts about excel-formula