SUMPRODUCT with COUNTIF returns incorrect value

%3CLINGO-SUB%20id%3D%22lingo-sub-3334522%22%20slang%3D%22en-US%22%3ESUMPRODUCT%20with%20COUNTIF%20returns%20incorrect%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3334522%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20formula%26nbsp%3B%3D(SUMPRODUCT(1%2FCOUNTIF(Hello!A2%3AA9993%2CHello!A2%3AA9993%26amp%3B%22%22)))-1%3C%2FP%3E%3CP%3Ebut%20it%20returns%20the%20incorrect%20math%20(values%20%2B1)%3C%2FP%3E%3CP%3EIf%20I%20change%20my%20formula%20to%26nbsp%3B%3D(SUMPRODUCT(1%2FCOUNTIF(Hello!A2%3AA9993%2CHello!A2%3AA9993%26amp%3B%22%22)))-2%20it%20returns%20the%20correct%20value.%3C%2FP%3E%3CP%3EAny%20suggestions%20as%20to%20why%20this%20is%20happening%20with%20my%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3334522%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3336278%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%20with%20COUNTIF%20returns%20incorrect%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3336278%22%20slang%3D%22en-US%22%3EPerhaps%20one%20of%20the%20cells%20has%20a%20space%20and%20is%20not%20actually%20empty%3F%20It%20appears%20you%20want%20to%20count%20the%20unique%20cells%20that%20are%20not%20blank%2C%20try%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3D(SUMPRODUCT((TRIM(A2%3AA9993)%26lt%3B%26gt%3B%22%22)%2FCOUNTIF(Hello!A2%3AA9993%2CHello!A2%3AA9993%26amp%3B%22%22)))%3CBR%20%2F%3E%3CBR%20%2F%3EOr%2C%20if%20you%20have%20office%20365%20or%202021%2C%20then%20I%20believe%20you%20should%20be%20able%20to%20use%20the%20unique%20function%3A%3CBR%20%2F%3E%3DSUM(--(LEN(TRIM(UNIQUE(Hello!A2%3AA9993)))%26gt%3B0))%3C%2FLINGO-BODY%3E
New Contributor

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))