Forum Discussion

Deepak Sharma's avatar
Deepak Sharma
Copper Contributor
Mar 21, 2018
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

 

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 21, 2018

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Hi Deepak,

     

    That could be like

    =SUMPRODUCT((C7:C16="Site1")/COUNTIF(D7:D16,D7:D16))
    • Deepak Sharma's avatar
      Deepak Sharma
      Copper 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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        When one more filter

        =SUMPRODUCT((C7:C16="Site1")*(D7:D16<>"")/COUNTIF(D7:D16,D7:D16&""))

Resources