# 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?
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...
0
Group Finance ManagerCommented:
Try

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

0
Author Commented:
This doesn't seem to work.
0
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.
0
Commented:
Roos,

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

Saurabh...
0
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
0
Group Finance ManagerCommented:
Here's an example workbook
0
Commented:
see attached file.
Book2.xlsx
0
Commented:
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
``````
0
Commented:
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.
0
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
0
Author Commented:
Roy this works in your spreadsheet, but not when I copy and paste the formula into mine. Any ideas?
0
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...
0
Check my post (#a40717125). You don't want to copy and paste, but rather, type the formula manually.
0
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
0
Commented:
Use this formula....

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

Saurabh...
MYFILE.xlsx
0
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...
0
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
``````
0
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))
``````

Enclosed is your file....

Saurabh...
MYFILE.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.