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 i want to find unique invoice numbers for site1 only separately, by using 1 more IF Function in above but couldn't succeeded .
Please help.
| A | B | |
| 6 | SITE NAME | Invoice Number |
| 7 | Site1 | 7000522916 |
| 8 | Site1 | 7000528150 |
| 9 | Site1 | 7000528143 |
| 10 | Site1 | 7000528143 |
| 11 | Site1 | 7000529922 |
| 12 | Site2 | 7000531741 |
| 13 | Site2 | 7000531741 |
| 14 | Site2 | 7000531753 |
| 15 | Site2 | 7000531762 |
| 16 | Site2 | 7000531969 |
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.
9 Replies
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&""))
