SUMPRODUCT with COUNTIF returns incorrect value

Deleted
Not applicable

I am using the formula =(SUMPRODUCT(1/COUNTIF(Hello!A2:A9993,Hello!A2:A9993&"")))-1

but it returns the incorrect math (values +1)

If I change my formula to =(SUMPRODUCT(1/COUNTIF(Hello!A2:A9993,Hello!A2:A9993&"")))-2 it returns the correct value.

Any suggestions as to why this is happening with my formula?

1 Reply
Perhaps one of the cells has a space and is not actually empty? It appears you want to count the unique cells that are not blank, try:

=(SUMPRODUCT((TRIM(A2:A9993)<>"")/COUNTIF(Hello!A2:A9993,Hello!A2:A9993&"")))

Or, if you have office 365 or 2021, then I believe you should be able to use the unique function:
=SUM(--(LEN(TRIM(UNIQUE(Hello!A2:A9993)))>0))