Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
How do I get a sum of every seventh cell in a column? | Excel Worksheet Functions | |||
how to remove every seventh column from excel | Excel Discussion (Misc queries) | |||
Skipping cells in a formula | Excel Programming | |||
How do I delete every seventh row? | Excel Worksheet Functions |