CODEWITHSUNDEEP
excel-formula
wotaskd
wotaskd

Reputation: 945

Excel - Counting unique values that meet multiple criteria

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 apples. How can I add that extra criteria?

Upvotes: 3

Views: 6941

Answers (1)

flipflop99
flipflop99

Reputation: 26

Mine is done a slightly different way (and it works), but the main reason I am posting this is because I was wondering if the second part of my formula could somehow be made into an array formula thus making it shorter.

To do this, sort the 3 columns on column B from smallest to largest and since part of it is currently an array formula, type in the formula and [ctrl]+[shift]+[enter]. The sorted data now looks like this:

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

And the formula is:

={SUM((B3:B9>0)*(C3:C9="orange"))+SUM((B3:B9>0)*(C3:C9="apple"))}-{IF(AND(OR(C3="orange",C3="apple"),OR(C4="orange",C4="apple"),B3=B4),1,0)+IF(AND(OR(C4="orange",C4="apple"),OR(C5="orange",C5="apple"),B4=B5),1,0)+IF(AND(OR(C5="orange",C5="apple"),OR(C6="orange",C6="apple"),B5=B6),1,0)+IF(AND(OR(C6="orange",C6="apple"),OR(C7="orange",C7="apple"),B6=B7),1,0)}

The first part (in curly braces) is pretty self-explanatory:

SUM((B3:B9>0)*(C3:C9="orange"))+SUM((B3:B9>0)*(C3:C9="apple"))

What I am interested in is trying to see if the second part can be expressed as an array formula. I had initially come up with

=SUM((AND((C3:C9="orange"),OR(C4:C9="orange",C4:C9="apple")))*(B3:B9=B4:B10)*(B3:B9<>""))

but it seems to be counting "banana" as a legit entry for some reason. Therefore, I had to go with entering all elements in the 2nd part manually rather than use an array formula. Anyone have ideas as to how to get this done?

Upvotes: 1

Related Questions