How to repeatingly sum the next n cells?
 

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!

How to repeatingly sum the next n cells?
 

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))

--
Regards
Roger Govier

wrote in message
...
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!

How to repeatingly sum the next n cells?
 

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"??

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


" wrote:

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!

How to repeatingly sum the next n cells?
 

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


--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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))

--
Regards
Roger Govier

wrote in message
...
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!

How to repeatingly sum the next n cells?
 

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