Forum Discussion

ingezwetsloot's avatar
ingezwetsloot
Copper Contributor
Aug 15, 2024

Count unique values including specific character

Hi, please help... 

 

Which formula can I use to create the numbers in yellow?

From column A I would like to count the unique values containing a "P"

Thank you already for helping! :smile:

 

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 15, 2024

    ingezwetsloot 

    In D3:

    =COUNTA(UNIQUE(FILTER(A2:A18, ISNUMBER(SEARCH("P", A2:A18)))))

    In D4:

    =COUNTA(UNIQUE(FILTER(A2:A18, ISERROR(SEARCH("P", A2:A18)))))

  • SaniGarba's avatar
    SaniGarba
    Brass Contributor
    Aug 15, 2024
    I am sure there will be more elegant solutions out there, but this is mine:
    Create additional column B. It's ideal to turn the data into a table. In column B, which I call P VALUE, enter this formula: =LEN([@[TOP ID]]) - LEN(SUBSTITUTE([@[TOP ID]], "P","")). It will calculate the number of characters of each word (x), then calculate the number of characters that do not include "P" (y). Wherever x = y you get 0, else 1 indicating presence of "P". Now in say, Cell D, use the UNIQUE array formula on the entire table. This will give you two columns of 8 unique values.

    Then, in any two adjacent columns, for UNIQUE WITH P use the COUNTIF on the second column as follows: =COUNTIF(E2:E9,1) you will get 3; for UNIQUE WITHOUT P enter =COUNTIF(E2:E9,0) you will get 5.
  • djclements's avatar
    djclements
    Bronze Contributor
    Aug 15, 2024

    ingezwetsloot In the interest of "best practices", it's generally not advisable to combine COUNTA with FILTER, because it's incapable of returning 0 when the FILTER function does not find any matching records. For example, if you were to count unique values containing "Z" in this same scenario, it would return 1 even though no records contain the letter "Z". There are plenty of documented workarounds (e.g. using IFERROR and ROWS instead of COUNTA); however, in this particular scenario, you could also try either of the following...

     

    //unique count containing "P" (not case-sensitive)
=SUM(N(ISNUMBER(SEARCH("p", UNIQUE(A2:A18)))))

//unique count does not contain "P" (not case-sensitive)
=SUM(N(ISERR(SEARCH("p", UNIQUE(A2:A18)))))

//unique count containing "P" (case-sensitive)
=SUM(N(ISNUMBER(FIND("P", UNIQUE(A2:A18)))))

//unique count does not contain "P" (case-sensitive)
=SUM(N(ISERR(FIND("P", UNIQUE(A2:A18)))))

     

    Cheers!

Resources