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.