# Solved: Excel - reference a value (contents of cell) for use in dynamic part of range



## HOBOcs (Jan 5, 2004)

Excel question: 

Testing - I have a column of numbers where I do a countif (A10:A365,A1)
In this case A1 = "1" and the column contains various number from 1-10.
I want to count the number of times 1 appears in the column up to row 100

I want to substitute "A365" with a value from cell "A2" ie, A2 = 100 and make it A100
So I'd like the countif logic to be countif(A10:A100, A1)

Then if I change A2 to 150 I get the range from A10:A150.

My issue is this range is used in a number of different caluclations and I'd like to dynamically change the rows to search.

Any help/direction appreciated.


----------



## bomb #21 (Jul 1, 2005)

Try this in place of your original COUNTIF:

=COUNTIF(INDIRECT("A10:A"&A2),A1)

(the section "A10:A"&A2 builds a literal string that "responds to" A2 value ; then you just throw in INDIRECT to make it work "for real")

Rgds,
Andy

EDIT: you may prefer a different approach:

=COUNTIF(var_Range,A1)

, where the defined name var_Range refers to:

=OFFSET(Sheet1!$A$10,0,0,Sheet1!$A$2-9,1)

You can test the dynamic range "effect" by entering 12 in A2, pressing F5, typing *var_Range* and pressing Enter. Then repeat with 15 in A2. Etc., etc.


----------



## HOBOcs (Jan 5, 2004)

Perfect exactly what I was looking for.

Used the "=COUNTIF(INDIRECT("A10:A"&A2),A1)"
I'll check out your other tips later but this has got me going.

I was thing about string manipulation but thought there might be an easier way.

:up: 
Much appreciated Andy 

Marking solved.


----------



## bomb #21 (Jul 1, 2005)

You _may_ find the other option more suitable in respect of "used in a number of different calculations". Either way, enjoy.


----------

