How to repeatingly sum the next n cells?

[luctue]

Hello,

My data is organized in a worksheet like this:

1 jan | 2 jan | 3 jan | 4 jan | 5 jan | 6 jan | 7 jan | 8 jan | 9 jan
| 10 jan | 11 jan | 12 jan | 13 jan | 14 jan | etc.
| | | | | |
| | X | X | | | X
| | etc

So, in the first row, dates are stated and in the second row data is
filled. Now I want to count the blanks per week, so the result will be
a table like this:

week 1 | week 2 | week 3 | etc.
7 | 4 | 6 | etc.

Using the formula COUNTBLANK(A1:A7) doesn't seem to be the solution,
because dragging this formula to the right leads to COUNTBLANK(A2:A8)
instead of the correct calculation COUNTBLANK(A8:A14).
Can somebody help me with the correct formula?

Thanks!

 

[Roger Govier]

Hi

Surely if your data is going across the page, then you want
A2:G2 for your first 7 days, then H2:N2 etc.

Try
=COUNTA(OFFSET($A$2:$G$2,,(COLUMN(A1)-1)*7))

 

[Luke M]

To do what you say...sorta:
=COUNTBLANK(INDIRECT("A"&-6+7*COLUMN(A1)&":A"&7*COLUMN(A1)))

If you copy this to the right, it will refere to A8:A14, then A15:A21.
Your question is confusing as you say "copy to the right". Copying
left/right changes the column, not the row.
But your example clearly has you changing the row, so that is what this
formula will do.

My main point is, are you wanting the reference to change from "A1:A7" to
"A8:A14", or do you really want "A1:G1" to change to "H1:N1"??

 

[Roger Govier]

Sorry that is counting the X's, you wanted the blanks so
=COUNTBLANK(OFFSET($A$2:$G$2,,(COLUMN(A1)-1)*7))

 

[luctue]

Thanks guys,
I got it working. Thanks a lot for your help!
Regards,
Luc