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