SUM FORMULA- skipping every seventh row
 

hi, i am trying to create a formula that adds up six cells in a column and gives a total in a different table, then i need it to skip the seventh cell and then add the next six and give another total, and so on..
I need a formula that i can drag down and have it autofill pretty much forever.

Im on a steep excel learning curve, can someone please explain how i write a formula to skip the seventh cell?

cheers

Assuming you need only one total sum of every six cells down a column, then one was way to do this is to have a conditional sum or the SUMIF function. What you'll need is a corresponding column with say a 'Y' for six cells that are in the same row as the data you wish to add, blank (or anything else by 'Y') in the seventh and so on.

So say the data you want to sum every six cells from is in A1:A256, and column E is blank in the worksheet, then enter a 'Y' (without the apostrophe) in E1:E6, E8:E13, E15:E20 and so on. Then use the formula sumif(E1:E256,"Y",A1:A256) wherever it is you wish to show the sum.

The column with the Y's could be anywhere in the sheet, so long as they correspond to the same row of the data column, so you can keep it far away from view if you like. Also, instead of using the range only to 256, you can extend it to say 25000 to save on updating the formula every time your data set grows.

There are other ways of doing this, but this is simple and works.

SUM FORMULA- skipping every seventh row
 

bite_tony was thinking very hard :
hi, i am trying to create a formula that adds up six cells in a column
and gives a total in a different table, then i need it to skip the
seventh cell and then add the next six and give another total, and so
on..
I need a formula that i can drag down and have it autofill pretty much
forever.

Im on a steep excel learning curve, can someone please explain how i
write a formula to skip the seventh cell?

=NOT(MOD(ROW(A7),6))+SUM(A$1:A7)
OR
=NOT(MOD(ROW(A7),6))+SUM(A1:A7)
Formulas to be copied in every 7th cells.

OR
=--NOT(MOD(ROW(A1),7))*(NOT(MOD(ROW(A1),6))+SUM(A1:A7 ))
Formula to be copied in every cells

Uncheck "Show a zero in cells that have zero value"

Bruno