Forum Discussion
Count unique values among duplicates with condition
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.
Many Thanks Sergei,
This works !
Can I replace the above 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))__ which I am Currently using to count unique values in entire col by using SUMPRODUCT.
Deepak,
In general yes, the formula for entire set will be like
=SUMPRODUCT((D7:D16<>"")/COUNTIF(D7:D16,D7:D16&""))
However, the formula with FREQUENCY shall be faster. If performance with COUNTIF is not good on your array better to keep old one. Another story array formula with SUM/FREQUENCY perhaps could be changed on regular SUMPRODUCT/FREQUENCY, I'll try to play.
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.
Sergei,
FREQUENCY will give us more precise results, SUMPRODUCT fails when we have common invoices in Site1 and Site2.
See screenshot