Indirect II

F

Felix

Is it possible to combine the formula

=COUNT.IF($B$5:$B$44;"*("&$AN5&")*")

with “INDIRECT†in order to change the 44 in B44 into any (line-)number?
The numbers will be placed in cell AO37.

Thank you.
 
B

Bob Phillips

You could use

=COUNTIF(OFFSET(B5,0,0,$AM5,1);"*("&$AN5&")*")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
F

Felix

Sorry, I cannot get it...

Maybe I should have explained better:

in the formula =COUNT.IF($B$5:$B$44;"*("&$AN5&")*")

what changes is the 44 (line)

I need this number used in the formulae to be in cell AO37.

Sorry for the confusion.
 
B

Bob Phillips

That should work fine

=COUNTIF(OFFSET(B5,0,0,$AO37,1);"*("&$AN5&")*")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
F

Felix

Hi Bob,


I could make it work, somehow...but I have to enter the line plus 1! This
means that the range is one line shorter. No idea why.

I can live with that, however, I simply use cell AO38 as the input and in
cell AO37 I use the formula =AO38+1.
 
Top