How to change cell reference for countif formula for every nth row

Instead of D$2:D$5 in your formula, you can use this in the formula in E2:

INDEX(D:D,INT((ROWS($1:1)-1)/4)*4+2):INDEX(D:D,INT((ROWS($1:1)-1)/4)*4+5)

You will need to make the substitution 3 times, and then you can copy the formula down as required.

Hope this helps.

Pete

Hey Pete,
The formula works like magic!! Thanks a lot.

I am new to excel and do not understand INDEX and INT function. If possible could you please briefly explain it?

P.S It is not urgent and is not of priority! Only if you can

Thanks again!!

Glad to help, Abis, and thanks for the rep.

You will notice that the formula is of the type:

=INDEX(...) : INDEX( ... )

and so basically the first INDEX will return the first cell reference and the second INDEX the final cell reference in column D. The bit that changes is the row reference, in that the first one adds 2 to the calculation given by the INT term and the second one adds 5 to that calculation. To understand what the INT term is doing, you can put this in a blank cell somewhere:

=INT((ROWS($1:1)-1)/4)*4+2

and copy this down a few rows. You will see that it returns 2 on the first few rows, then 6 for the next few, then 10, and so on. The ROWS($1:1) term initially returns 1 (i.e. the number of rows between the two references), but notice that the first reference is anchored to row 1, so when this is copied down it becomes ROWS($1:2) (which returns 2), then ROWS($1:3) (returning 3), then ROWS($1:4) and so on, on successive rows, and so basically it generates a sequential number for each row. 1 is subtracted from this (giving a sequence 0, 1, 2, 3, 4, 5, etc.), and then that is divided by 4 within the INT function (which returns the integer, or whole number, part). This, then gives rise to a sequence 0, 0, 0, 0, 1, 1, etc., which is then multiplied by 4 and has 2 added on, which gives the sequence 2, 2, 2, 2, 6, 6, 6, 6, 10, 10, and so on. and these are the initial cell references that you want. The other term operates in a similar way, but generates the sequence 5, 5, 5, 5, 9, 9, 9, etc., which corresponds to the second cell reference that you want.

Hope this helps.

Pete

Thanks again Pete!! This helps a lot.

Cheers,

Abis