Forum Discussion
Solved
Count unique values among duplicates with condition
Hi
I can count unique invoice Numbers in col B using
array function =SUM(IF(FREQUENCY(IF(LEN(D7:D16)>0,MATCH(D7:D16,D7:D16,0),""),IF(LEN(D7:D16)>0,MATCH(D7:D16,D7:D16,0),""))>0,1))
but...
Deepak,
You may add the condition like this
=SUM(IF(FREQUENCY(IF((C7:C16="Site1")*(LEN(D7:D16)>0),MATCH(D7:D16,D7:D16,0),""),IF((C7:C16="Site1")*(LEN(D7:D16)>0),MATCH(D7:D16,D7:D16,0),""))>0,1))
However, both formulas give the same result, please see on screenshot and attached.
Right Sergei,
SUMPRODUCT/COUNTIF is also counting empty cells.
it'd be useful if we can make my array with condition (count unique only for Site1 excluding blanks), i don't want to count empty cells.
Deepak,
You may add the condition like this
=SUM(IF(FREQUENCY(IF((C7:C16="Site1")*(LEN(D7:D16)>0),MATCH(D7:D16,D7:D16,0),""),IF((C7:C16="Site1")*(LEN(D7:D16)>0),MATCH(D7:D16,D7:D16,0),""))>0,1))
However, both formulas give the same result, please see on screenshot and attached.
Hi Deepak,
Yes, with repeated for different sites invoice the formula doesn't work.
Sergei,
FREQUENCY will give us more precise results, SUMPRODUCT fails when we have common invoices in Site1 and Site2.
See screenshot