Thread: Counting Cells with Variable Reference

I noticed you're using INDIRECT - are you physically removing and inserting the Raw Data sheet each day ?

Having looked at your file ... if you insert a column between B & C (so old C becomes D and new C is blank) you can keep things simple... eg

Code:

D2:
=COUNTIF(INDEX(INDIRECT("'Raw Data'!A:A"),2+SUM($B$1:$B1)):INDEX(INDIRECT("'Raw Data'!A:A"),1+SUM($B$1:$B2)),"<"&IF(ISNUMBER(0+E$1),0+E$1,5000000))-SUM($C2:C2)

The above can be applied across matrix D2:G11

Hope that helps

(Thread moved to Worksheet Functions Forum)

My point re: INDIRECT is that if you're not deleting the sheet itself you don't need to use INDIRECT (it is a Volatile function)

A non-volatile version of that in the prior post would be:

Code:

D2: 
=COUNTIF(INDEX('Raw Data'!$A:$A,2+SUM($B$1:$B1)):INDEX('Raw Data'!$A:$A,1+SUM($B$1:$B2)),"<"&IF(ISNUMBER(0+E$1),0+E$1,5000000))-SUM($C2:C2)
applied across matrix D2:G11

yensid, apologies, but there was a typo in the prior example - the first INDEX should use 2+SUM(B$1:B1) rather than 1+SUM(B$1:B1) -- this is to account for first row of data being in row 2 rather than row 1. I have modified the prior examples accordingly.

In essence we're simply using a standard COUNTIF and a COUNTIF as you know works along the lines of:

Code:

=COUNTIF(range,criteria)

In our case our formula (for D2) looks like

Code:

D2:
=COUNTIF(INDEX('Raw Data'!$A:$A,2+SUM($B$1:$B1)):INDEX('Raw Data'!$A:$A,1+SUM($B$1:$B2)),"<"&IF(ISNUMBER(0+E$1),0+E$1,5000000))-SUM($C2:C2)

So you can see (by means of colour coding) that we use 2 INDEX functions to create the range and a subsequent IF in conjunction with & and < operator to create the criteria
(the last bit in Italics which sits outside of the COUNTIF is to ensure we're displaying only "periodic" movement - inappropriate term here in truth but one that epitomises the general principle)

So - range - in more detail...

Code:

INDEX('Raw Data'!$A:$A,2+SUM($B$1:$B1)):INDEX('Raw Data'!$A:$A,1+SUM($B$1:$B2))

You can see the INDEX calls are separated by a colon indicating we're using INDEX in this context to create range references - this may be different to how you've seen it used before ?

INDEX is a pretty flexible formula, eg:

Code:

=INDEX(A:A,10)

could be used to return contents of A10 or

Code:

=SUM(INDEX(A:A,10):A20)

could be used to create a range A10:A20

make sense ?

In your case the start row & end row for the range is determined by the rolling sum of the values in Column B, for ex. B2 = 51 ... this figure states we should look only at the first 51 rows of data in the table... we know first row of data is row 2.

So to establish starting cell we have:

Code:

INDEX('Raw Data'!$A:$A,2+SUM($B$1:$B1))

which says column is A and row is to be 2 + sum of prior values (ie excluding current row) in B which for row 2 will be 0 given B1 is non-numeric

To establish end row we have:

Code:

INDEX('Raw Data'!$A:$A,1+SUM($B$1:$B2))

which says column is A and row is to be 1 + sum of all values in B up to and including the current row which for row 2 will be 52 (1+51)

Thus we end up in essence with a range that says

Code:

INDEX(A:A,2):INDEX(A:A,52) --> A2:A52

When we move to the next row down in our records the start row & end row will update accordingly, eg:

B2: 51
B3: 168

So our start range for the COUNTIF in row 3 of our results table will be:

Code:

INDEX(A:A,2+51)  -> A53

And end row

Code:

INDEX(A:A,1+51+168) -> A220

Does that make sense ?

The criteria for the COUNTIF we set to be the value in row 1 in the column to the right of the cell containing the formula (ie less than next boundary), however, when it comes to the last column we have a problem given there is no limit in the next cell - to account for this we use the IF

Code:

"<"&IF(ISNUMBER(0+E$1),0+E$1,5000000)

so we say if the value in row 1 of the column to the right is non-numeric use 5000000 else use the value in row 1 of the column to the right.

to get this into a readable string we concatenate the "<" operator with this value by means of & ....

Code:

"<"&2000000 --> "<2000000"

Obviously this means in each column we're counting how many records are less than the limit ... but this will obviously include values that may be included in prior boundaries so we need to subtract the prior totals from the COUNTIF total

Code:

COUNTIF(....)-SUM($C2:C2)