# Sum every 8th cell in a column

I need a formula that will sum every 8th cell in a column please.
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Assuming you want to do it for A Column..and you want to Sum row-8,16,24 then you can use the following formula..

``````=SUMPRODUCT((MOD(ROW(A:A),8)=0)*(A:A))
``````

Saurabh...
Group Finance ManagerCommented:
Try

=SUMPRODUCT((MOD(ROW(\$A\$1:\$A\$500),8)=0)*(\$A\$1:\$A\$500))

Author Commented:
This doesn't seem to work.
Type (do not paste) this at the bottom of your column, and change the numbers as follows:

=SUM(IF(MOD(ROW(A1:A100),8) = 0, A1:A100))
Change A1 to the first cell in your column (i.e., B1, C2, etc).
Change A100 to the last cell in your column (i.e., B100, C750, etc).
Do NOT press ENTER to save.
Instead, press Ctrl + Shift + ENTER.
Commented:
Roos,

When you say doesn't work..Can you post your file where you are applying this formula..

Saurabh...
EngineerCommented:
Which row is the first one?

If the first row is 1 then change 0 to 1
If the first row is 2 then change 0 to 2

and so on
Group Finance ManagerCommented:
Here's an example workbook
Microsoft Excel ExpertCommented:
see attached file.
Book2.xlsx
Microsoft Excel ExpertCommented:
also you can use this UDF. put it in a module and then   =select the range and that is it.

=SumEveryEighth(YOURRANGE)

``````Function SumEveryEighth(MyRange As Range)
Dim x As Integer
SumEveryEighth = 0
For x = 1 To MyRange.Cells.Count
If (x Mod 8) = 1 Then
SumEveryEighth = SumEveryEighth + MyRange.Cells(x).Value
End If
Next x
End Function
``````
Microsoft Excel ExpertCommented:
assuming your data starts from A2

do the following =SUMPRODUCT((MOD(ROW(\$A\$2:\$A\$1000),8)=0)*(\$A\$2:\$A\$1000)+\$A\$2)

change the date as per your need.
Group Finance ManagerCommented:
I can't see the point in a UDF, no VBA code can be as efficient as an Excel inbuilt function.

It looks like my workbook didn't upload
sum8.xlsx
Author Commented:
Roy this works in your spreadsheet, but not when I copy and paste the formula into mine. Any ideas?
Commented:
Roostrerup...

Can you go and check formulas-->calculations options-->automatic as that need to be in automatic mode.

In additional can you check the forma the format of cell by pressing ctrl+1 and format that to general or a number....

If the above 2 doesn't resolve your issue can you post your sample workbook here..

Saurabh...
Check my post (#a40717125). You don't want to copy and paste, but rather, type the formula manually.
Author Commented:
here's is an example of the file.  Start at B2 and total every 8th cell.

Need the data it to stay in column B.
MYFILE.xlsx
Commented:
Use this formula....

``````=SUMPRODUCT((MOD(ROW(B2:B1154)-2,8)=0)*(B2:B1154))+B2
``````

Saurabh...
MYFILE.xlsx
Commented:
Also on other note it will be easy by doing A Column..I'm assuming all of these values have the same header..If that's the case then you can use that to get the sum of what you are looking for...
Well, this is a little different than how it was interpreted in your opening post. Rather thatn "a formula that will sum every 8th cell in a column," your spreadsheet needs to add the SUM formula at every 8th row, in order to sum the detail only in those rows above it.

This macro will work:
``````Option Explicit
Public Sub Sum8th()
Dim Max As Integer
Dim Row As Integer

Max = Range("B32767").End(xlUp).Row
Row = 0

Do While Row < Max
Row = Row + 8
Cells(Row, 2).Formula = "=SUM(" & (Row - 6) & ":" & (Row - 2) & ")"
Cells(Row, 2).Font.Bold = True
Loop
End Sub
``````
Commented:
And my bad you don't have to add B2 at the end you can just do this...

``````=SUMPRODUCT((MOD(ROW(B2:B1154)-2,8)=0)*(B2:B1154))
``````

Saurabh...
MYFILE.xlsx

Experts Exchange Solution brought to you by