# countif using input cell as range depth

• Lets say i have numbers in 5 columns H - M and the number to count in range is in AD3
is there a way to determin the range depth "\$M\$9" by using an input cell "AH3", so if i enter 100 in cell "AH3"
The formula will change from

To

Thanks
Nigel

• Did you try using the INDIRECT function?

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• I have searched the net for help with INDIRECT function without any luck so far

Nigel

• Really??? There's loads of help out there, including this tutorial: https://www.contextures.com/xlFunctions05.html

Give this a try:

https://support.office.com/en-…26-4f44-b491-92b6306fa261

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• I have searched the net for help with INDIRECT function without any luck so far
Nigel

• I got it using =COUNTIF(H2:INDIRECT(CONCATENATE("L",\$S\$3)),O3) thanks for pointing me in the right direction Ali

Nigel

:ole: