Jul 07 2020 05:29 AM - edited Jul 07 2020 05:39 AM
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!
Jul 07 2020 06:02 AM
Solution
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))
Jul 07 2020 06:18 AM
It works, thank you so much!!!!
Jul 07 2020 06:22 AM
As variant with dynamic arrays
list of such customers
=UNIQUE(FILTER(B5:D11,(D5:D11="Yes")*(C5:C11>=5)))
and number
=ROWS(F5#)
assuming same customer - same class.
Jul 07 2020 09:14 AM
You're welcome @Tina_Charbon!
Jul 07 2020 06:02 AM
Solution
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))