Countif using Indirect with concatenate inside?

[u0107]

Hello all,

I have this formula in a cell which gives me a #REF error:

=COUNTIF(INDIRECT(CONCATENATE("'Working area'!",ADDRESS(6,13),":","'Working area'!",ADDRESS(6,742))),"N")

The concatenated text string calculates to

'Working area'!M6:ABN6

In that range I would like to count the occurrences of the letter N.

(I have simplified the ADDRESS function to ADDRESS(6,13). In my real spreadsheet, the number 6 is obtained by some more function-combinations.)


Thank you in advance.

Cheers!

Uttam

 

[pgc01]

Hi Uttam

The concatenated text string calculates to

'Working area'!M6:ABN6

No, it doesn't. It calculates to:

'Working area'!$M$6:'Working area'!$ABN$6

and that's wrong syntax.

Try:

=COUNTIF(INDIRECT(CONCATENATE("'Working area'!",ADDRESS(6,13),":",ADDRESS(6,742))),"N")