0

I'm trying to create an excel function that counts the number of unique entries in a range of (potentially merged) cells. The UNIQUE function returns two values for merged cell, one of which is zero. This seems easy enough, just count the cells that aren't equal to zero. However, all of the following formulas result in the generic "There's a problem with this formula" error: COUNTIF(UNIQUE(A2:P2,TRUE)<>0,TRUE) COUNTIF(UNIQUE(A2:P2,TRUE),"<>0") COUNTIF(NOT(UNIQUE(A2:P2,TRUE)=0),TRUE)

What's especially infuriating is that if I put the first argument of COUNTIF in another cell (say R2), and refer to the spill range (i.e. COUNTIF(R2#,TRUE)), all 3 of those formulas work fine. What on earth is happening here?

Improve this question
1
  • 1
    There is nothing wrong with the error its natural phenomenon of any IFs family function, as per the COUNTIFS() or COUNTIF() function that first criteria states to take range and not array here, UNIQUE() is returning an array. Try using SUM() function
    – Mayukh Bhattacharya
    Commented Jan 25 at 21:34

1 Answer 1

Reset to default
0

There is nothing wrong with the error its natural phenomenon of any IFs family functions, as per the COUNTIFS() or COUNTIF() function that first criteria/parameter states to take range and not array here, UNIQUE() is returning an array. Try using SUM() function.

COUNTIF() syntax as per MSFT Documentations: Read Here

enter image description here

Instead use SUM() as below:

=SUM(N(UNIQUE(A2:P2,TRUE)<>0))

Or,

=SUM(--(UNIQUE(A2:P2,1)<>0))

in place N() one can also use double unary --

Or to exclude the blanks before using UNIQUE() one can use TOROW()

=COUNTA(UNIQUE(TOROW(A2:P2,3),1))

In the MSFT documentation it mentions the first criteria Range can contain arrays which means an array can be used as a range when placed in a different column or as in helper columns which can be referenced, it doesn't say it can be used within the function directly, there is a difference in it, to explain, here is a .gif

enter image description here

Improve this answer
6
  • Wow the documentation is misleading. "Range can contain numbers arrays, a named range, or references that contain numbers" Thank you for the suggestions. I ended up going with the first solution I could get to work, which was =LET(foo,UNIQUE(A32:P32,TRUE)<>0,COUNTA(FILTER(foo,foo)))
    – pkujawa
    Commented Jan 25 at 22:45
  • 1
    I understand now. There's also an error in my solution above: if all values in the range are unique, it returns 1 less than it should. I will use =COUNTA(UNIQUE(TOROW(A2:I2,3),1)) instead :)
    – pkujawa
    Commented Jan 25 at 23:01
  • 1
    Be aware that TOROW(range,3) can be unreliable in case the range contains calculation results from formulas. They may be excluded from the result when they recalculate. (More info here: stackoverflow.com/q/77563158/12634230 )
    – P.b
    Commented Jan 26 at 20:03
  • 1
    Then this should be the right way to reach =SUM(--(UNIQUE(A2:P2,1)<>0)) at goal!
    – Mayukh Bhattacharya
    Commented Jan 26 at 20:10
  • 1
    Nice one @MayukhBhattacharya and =SUM(N(UNIQUE(A2:P2,1)<>"")) for alphanumeric.
    – P.b
    Commented Jan 26 at 22:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .