2

Recently, I had data of about 150,000 rows, where about 100,000 rows had a unique value in column E. Column looked like:

Column Heading
1
2
2
3
...
99999
99999
100000

To count the unique rows, I attempted COUNT(UNIQUE(E:E))-1 and received an output of "-1". However, due to a happy accident, I found that COUNT(UNIQUE(E:E)+0)-1 gave me my expected output of "100000". I rarely use dynamic array formulas, so I wanted to learn about why this made a difference.

I thought the difference might be attributable to my use of the entire column, as I know that can be problematic with COUNTIFS/SUMIFS sometimes, but COUNT(UNIQUE(E1:E150001))-1 still yields "-1", and COUNT(UNIQUE(E1:E150001)+0)-1 still yields "100000".

Could someone please explain to me this behavior? How does the "+0" within the COUNT() function "fix" my problem? What purpose is the "+0" serving?

Thank you so much for your help!

Improve this question
2
  • I'm guessing that your numbers are really text and not values. ` COUNT()` only works on values. Adding -0- recasts each item in the array back as values.
    – gns100
    Commented Jan 4, 2022 at 19:48
  • You're totally right, and I'm embarassed to have overlooked it. However, I switched to COUNTA and noticed something interesting: An interesting quirk - using COUNTA(UNIQUE(E:E)) gets me "100002" - presumably it's counting the blank cells beneath my data as a unique value, but I think it's interesting that it apparently doesn't do this when I use COUNT(UNIQUE(E:E)+0).
    – Ricky Barz
    Commented Jan 4, 2022 at 19:54

1 Answer 1

Reset to default
1

Your data in column E is probably stored as text. As COUNT function only counts numeric values, it'll return 0.

When you add 0, Excel "adds" it to each value, and also it converts them to numbers, so COUNT will give the correct result.

You could also get correct result with COUNTA; or making sure data is stored in the right format in the first place.

Improve this answer
1
  • You are correct, and I should have figured that out. Thank you! An interesting quirk - using COUNTA(UNIQUE(E:E)) gets me "100002" - presumably it's counting the blank cells beneath my data as a unique value, but I think it's interesting that it apparently doesn't do this when I use COUNT(UNIQUE(E:E)+0).
    – Ricky Barz
    Commented Jan 4, 2022 at 19:47

You must log in to answer this question.

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