SOLVED

Count, but no duplicates and under 2 conditions

Copper Contributor

Hi, I have an Excel file with 3 columns. 

 

Colum A contains customer numbers. the customer numbers may appear several times.

Column B contains numbers from 1-9

Column C either contains YES or is empty

 

I want to Count the customers in Colum A - BUT W/O Duplicates, so every customer just once, no matter how often they appear), IF colum B > 5 and IF colum C contains a YES

 

I tried with Arrays, combining Formulars... cannot get there. I have attached an exampkle table where the result should be 2.

 

Thanks in Advance!

4 Replies
best response confirmed by Tina_Charbon (Copper Contributor)
Solution

@Tina_Charbon 

 

How about this Array Formula which requires confirmation with Ctrl+Shift+Enter and not Enter only.

 

=SUM(--(FREQUENCY(IF(B2:B8>=5,IF(C2:C8="YES",MATCH(A2:A8,A2:A8,0))),ROW(A2:A8)-ROW(A2)+1)>0))

 

countunique.jpg

 It works, thank you so much!!!!

@Tina_Charbon 

As variant with dynamic arrays

image.png

list of such customers

=UNIQUE(FILTER(B5:D11,(D5:D11="Yes")*(C5:C11>=5)))

and number

=ROWS(F5#)

assuming same customer - same class.

1 best response

Accepted Solutions
best response confirmed by Tina_Charbon (Copper Contributor)
Solution

@Tina_Charbon 

 

How about this Array Formula which requires confirmation with Ctrl+Shift+Enter and not Enter only.

 

=SUM(--(FREQUENCY(IF(B2:B8>=5,IF(C2:C8="YES",MATCH(A2:A8,A2:A8,0))),ROW(A2:A8)-ROW(A2)+1)>0))

 

countunique.jpg

View solution in original post