SUMPRODUCT #DIV/0! Error When Referencing Different Worksheet

[NoMotion]

I am using "=SUMPRODUCT((D2:D38<>"")/COUNTIF(D2:D38,D2:D38&""))" to generate a count of any cells with a numerical or text value in D2:D38, excluding duplicate and blank cells. This works fine as long as I use it in the same worksheet I am counting in. If I use that formula in a new worksheet, and try to generate a count from the original worksheet I end up with a divide by zero error. Here is the formula that produces an error, with 'Master List' being the name of the original worksheet:

=SUMPRODUCT(('Master List'!D2:D38<>"")/COUNTIF('Master List'!D2:D38,D2:D38&""))

Is there a problem with the syntax, or should is there a better formula to use?


Thank you.

[AlKey]

Try it like this

=SUMPRODUCT(('Master List'!D2:D38<>"")/COUNTIF('Master List'!D2:D38,'Master List'!D2:D38&""))

[NoMotion]

Perfect! Thank you! It was just a syntax error.

[AlKey]

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).