# 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

Contents

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

AB
14320
21943W

## Solution – 1

try:

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

or just:

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

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.