Sum every 8th cell in a column

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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Saurabh Singh TeotiaCommented:
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))

Open in new window


Saurabh...
Roy CoxGroup Finance ManagerCommented:
Try

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

Adjust the range
roosterupAuthor Commented:
This doesn't seem to work.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

dsackerContract ERP Admin/ConsultantCommented:
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.
Saurabh Singh TeotiaCommented:
Roos,

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

Saurabh...
Saqib Husain, SyedEngineerCommented:
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
Roy CoxGroup Finance ManagerCommented:
Here's an example workbook
ProfessorJimJamMicrosoft Excel ExpertCommented:
see attached file.
Book2.xlsx
ProfessorJimJamMicrosoft 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

Open in new window

ProfessorJimJamMicrosoft 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.
Roy CoxGroup 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
roosterupAuthor Commented:
Roy this works in your spreadsheet, but not when I copy and paste the formula into mine. Any ideas?
Saurabh Singh TeotiaCommented:
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...
dsackerContract ERP Admin/ConsultantCommented:
Check my post (#a40717125). You don't want to copy and paste, but rather, type the formula manually.
roosterupAuthor 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
Saurabh Singh TeotiaCommented:
Use this formula....

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

Open in new window




Saurabh...
MYFILE.xlsx
Saurabh Singh TeotiaCommented:
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...
dsackerContract ERP Admin/ConsultantCommented:
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

Open in new window

Saurabh Singh TeotiaCommented:
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))

Open in new window


Enclosed is your file....

Saurabh...
MYFILE.xlsx

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.

Start your 7-day free trial
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.