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!