Auto updating ranges based on different cell values

[wowakan]

Dear Help Board,

I have tried unsuccessfully to figure this out and am wondering if it can only be done with VBA or a macro?

I have the following formula and want to dynamically update the values AA9 and AA29 to either expand or contract my range in column AA.

=COUNTIFS(AA9:AA29,">0", AA9:AA29,"<1")

I also tried defining the ranges and named them SD_21, SD_13, etc.  However, I do not know how to automatically insert either "SD_21, SD_13, etc" into the below.

=COUNTIFS(SD_21,">0", SD_21,"<1")

Could really use your help...both the above work however, I want an easier way to change the range size from another cell value.....thank you!

[#2]

Where are the "cell values" based on which the range AA9:AA29 should be modified?

[#3]

@Doug_Robbins_Word_MVP 

Thanks for the reply.  The cell values are currency values.   I simply want to make a formula where I can adjust the range from ex.  1 to 10 from another cell that contains the values 1 and 10.  Is this possible?

Then by altering the cell values let's say from 1 to 10 I can adjust the range for example to 4 to 6 in the same row.

Thanks for any help you can offer as this has me stumped.

[#4 · Accepted solution]

@wowakan 

=COUNTIFS(INDIRECT(T23&":"&T24),">0",INDIRECT(T23&":"&T24),"<1")

Is this what you want to do? You can manually change the range by entering e.g. AA14 and AA29 in cells T23 and T24 as shown in the attached file.

[#5]

Thank you so much. I could not wrap my thoughts around the proper syntax. This is exactly what I was looking for!

All the best to you.