Search code examples
google-sheetsgoogle-sheets-formulaformulaspreadsheetcount-unique

Need to count unique but not count 43 and 43W as two different instances using Google Sheets Formulas

In the table below I am trying to get a COUNTUNIQUE formula working with INDIRECT("{RANGE}") that would show 3 unique numbers. Basically 43W and 43 need to reflect the same number. I need to do this without using a staging sheet, so basically a one-liner would be great.

At the moment I have the following formula, but it just treats 43 and 43W as the same object: =COUNTUNIQUE(INDIRECT("RESPONSES!S3:X"))

A B
1 43 20
2 19 43W

Thanks in advance!

Solution

  • try:

    =INDEX(LAMBDA(i, COUNTUNIQUE(SUBSTITUTE(i, "W", )))(INDIRECT("RESPONSES!S3:X")))

    enter image description here

    or just:

    =INDEX(COUNTUNIQUE(SUBSTITUTE(INDIRECT("RESPONSES!S3:X"), "W", )))

    enter image description here