Excel - count unique values ​​matching multiple criteria

Question

I am trying to use a function to count the number of unique cells in a spreadsheet that at the same time meet several criteria.

In 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 above example, I need to know how many unique stores with a valid STORE # received OR oranges. In the above case, the result should be 3 (stores 56089, 89247 and 45321).

This is how I started trying to solve the problem:

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

The above formula will give the number of unique stores with valid # storage, but not only those that received oranges or apples. How to add additional criteria?

Answer 1

- ( ), , , , , - , .

, 3 B , , [ctrl] + [shift] + [enter]. :

  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

:

={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)}

( ) :

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

, .

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

- , , "". , . - , ?