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

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

Problem Description:

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"))

Thanks in advance!

Solution – 1

try:

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

or just:

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