Using INDIRECT to create a dynamic Range

[williamh2k]

I'm attempting to create a dynamic range as part of a COUNTIF Statement and want to read the numerical part of the range from another cell.

The cell containing the number is A1 and the value is 10. I want to count all of the NON EMPTY Cells in the range B1:B10

I've tried the following and multiple other combinations but can't seem to get the syntax coorect

=COUNTA(B1:INDIRECT("B"&A1)

The outcome I'm trying to achieve is
=COUNTA(B1:B10). - where the highlighted part is dynamically read from another cell.

[etaf]

works for me
=COUNTA(B1:INDIRECT("B"&A1))

missing a bracket - but excel should have asked to autocorrect that for you

[williamh2k]

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

[romperstomper]

I'd use INDEX instead:

=SUMPRODUCT(1/COUNTIF('Sheet1 '!B1:INDEX('Sheet1 '!B:B,A1),'Sheet1 '!B1:INDEX('Sheet1 '!B:B,A1)))

You could shorten with LET