Unusual Quirk of UNIQUE() function and Dynamic Array formulas

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!

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.