Forum Discussion
Deepak Sharma
Mar 21, 2018Copper Contributor
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
- SergeiBaklanDiamond Contributor
Hi Deepak,
That could be like
=SUMPRODUCT((C7:C16="Site1")/COUNTIF(D7:D16,D7:D16))
- Deepak SharmaCopper Contributor
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.
- SergeiBaklanDiamond Contributor
When one more filter
=SUMPRODUCT((C7:C16="Site1")*(D7:D16<>"")/COUNTIF(D7:D16,D7:D16&""))