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.
Hi Deepak,
That could be like
=SUMPRODUCT((C7:C16="Site1")/COUNTIF(D7:D16,D7:D16))
Hi Sergei,
Thanks for response But that won't work if there are a blank cells.
Like:
| A | B | |
| 6 | SITE NAME | Invoice Number |
| 7 | Site1 | 7000522916 |
| 8 | Site1 | 7000528150 |
| 9 | Site1 | 7000528143 |
| 10 | Site1 | 7000528143 |
| 11 | Site1 | |
| 12 | Site2 | 7000531741 |
| 13 | Site2 | 7000531741 |
| 14 | Site2 | 7000531753 |
| 15 | Site2 | 7000531762 |
| 16 | Site2 | 7000531969 |
=SUMPRODUCT((C7:C16="Site1")/COUNTIF(D7:D16,D7:D16))
| #DIV/0! |
I have a 5000 line data which contains numbers, text, alphanumeric & empty cells in col B.
When one more filter
=SUMPRODUCT((C7:C16="Site1")*(D7:D16<>"")/COUNTIF(D7:D16,D7:D16&""))
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.