[HOBOcs]

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]

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]

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.


Much appreciated Andy

Marking solved.

[bomb #21]

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