Home » Forums » Open Source Learning Center » Question and Answer

Using formulae to automate cell referencing

Submitted by Avi_mufc on 20 August, 2015 - 10:19

Hi all

I have spreadsheets running with columns for each month in a year, for several years.

In each column I have a data set, and I have a COUNTA formula inserted in each column to determine the number of data instances in each column.
(There are no blanks)

In addition, in each column I have different COUNTIF formulas inserted.

Each column has a different number of rows of data.

The first formula I always refer to is say, C1=COUNTA(B10:B20017), which returns a value of 14166.

Currently, for each month, for each column I am typing in further formulas, say:
>> C2=COUNTIF(C10:C14175,"new")

For the *C10:C14175* reference I currently have two options:
>> Either highlight the range, or
>> Just manually type in the *C14175*, and in manually doing so, what I'm actually doing is taking the COUNTA result above of 14166, and mentally adding 9 to it, every time in each column.

What I would like to find out is if it is possible to have some sort of formula which would do this for me, something like:
C2=COUNTIF(C10:C(C1's result),"new")
>> This does not work.

Any ideas or suggestions?

Thanks in advance.

‹ Create a Macro that will delete a cell range on print Message from Web Page ›
»

Try this

Submitted by LeoTaxi on 20 August, 2015 - 23:23.

Try this instead

C2=COUNTIF(C10:INDIRECT("C"&C$1,"new"))

Kind regards
Leo

»

Thanks!

Submitted by Avi_mufc on 21 August, 2015 - 09:54.

Thanks for your response.

Your formula actually returned an error message.

Though when retyping it in, looking at the excel prompts, your formula was just missing a FALSE/TRUE argument before ,"new")

The correct formula works as follows, and thanks for pointing me in the right direction:

=COUNTIF(C10:INDIRECT("C"&(C$2+9),TRUE),"new")

This INDIRECT formula is a great, great help.

»