Thanks for the prompt reply, i had effectively over simplified my question without checking, apologies.

I'm effectively trying to Count unique values in a range with COUNTIF

Works
=SUMPRODUCT(1/COUNTIF('Sheet1 '!B1:B10,'Sheet1 '!B1:B10)). - the parts in bold i'd like to dynamically generate from cell A1.
Doesn't Work
=SUMPRODUCT(1/COUNTIF('Sheet1 '!B1:INDIRECT("B"&A1),'Sheet1 '!B1:INDIRECT("B"&A1)))

If I do this without the reference to another sheet i.e. the data and the total on the same sheet it works.

Works
=SUMPRODUCT(1/COUNTIF(B1: INDIRECT("B"&A1), COUNTIF(B1:INDIRECT("B"&A1))))