SOLVED

Count, but no duplicates and under 2 conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-1506158%22%20slang%3D%22en-US%22%3ECount%2C%20but%20no%20dublicates%20and%20under%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1506158%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20an%20Excel%20file%20with%203%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColum%20A%20contains%20customer%20numbers.%20the%20customer%20numbers%20may%20appear%20several%20times.%3C%2FP%3E%3CP%3EColumn%20B%20contains%20numbers%20from%201-9%3C%2FP%3E%3CP%3EColumn%20C%20either%20contains%20YES%20or%20is%20empty%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20Count%20the%20customers%20in%20Colum%20A%20-%20BUT%20W%2FO%20Duplicates%2C%20so%20every%20customer%20just%20once%2C%20no%20matter%20how%20often%20they%20appear)%2C%20IF%20colum%20B%20%26gt%3B%205%20and%20IF%20colum%20C%20contains%20a%20YES%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20with%20Arrays%2C%20combining%20Formulars...%20cannot%20get%20there.%20I%20have%20attached%20an%20exampkle%20table%20where%20the%20result%20should%20be%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20Advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1506158%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New 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
Highlighted
Best Response confirmed by Tina_Charbon (New 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

Highlighted

 It works, thank you so much!!!!

Highlighted

@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.

Highlighted