Help with INDIRECT function please

[Harryf]

Hi there everybody!

I simply cannot seem to win this one...

In a countifs function, I refer to the range in the function as being A2 to A8, i.e. Countifs(A2:A8;"whatever").

But I want to type the numbers of the starting row of the range (i.e. 2) and the ending row (8) in cells A10 and A11 and use these numbers to reference the actual range in the function instead of typing "A2:A8.

So, in cell A10, I will have 2 and in cell A11, I will have 8. In the countifs function, I would like to use something to concatenate the column number (A) in the function to the numbers in cells A10 and A11. So, the first A must be linked to 2 (becoming A2 in the function) while the second A must be linked to the 8 and reference A8...

I tried INDIRECT but cannot seem to get anything right...

Any help is sincerely appreciated.

Kind regards

Harry

 

[Joe4]

Try this:

=COUNTIF(INDIRECT("A" & A10 & ":A" & A11);"whatever")

 

[RoryA]

Personally, I'd prefer INDEX over INDIRECT:

=COUNTIF(INDEX(A:A;A10):INDEX(A:A;A11);"whatever")

 

[Harryf]

Hi Joe!

Nope, it gives me a #REF error...

 

[Harryf]

Hi Rory!

Your solution is PERFECT! Thank you!! Yay!!

Thanks for the try, Joe! We both learned something new... Cool...

 

[Joe4]

Hi Joe!

Nope, it gives me a #REF error...

Strange. I tried it out on my computer and it worked perfectly fine for me.