1

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!

Random206
  • 757
  • 6
  • 19
  • any reason why you use INDIRECT? – player0 Nov 23 '22 at 10:21
  • 1
    @player0 I have a macro that I run that removes all the data in the sheet each night and it messes up the cell references if I don't use it. Even if I use $A$1 referencing. – Random206 Nov 23 '22 at 10:24

1 Answers1

1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks so much, both of these work. I've gone with the second as it's easier to understand at a glance. – Random206 Nov 23 '22 at 10:40