Sum every 8th cell in a column

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))

Select allOpen in new window

Saurabh...

Try

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

Adjust the range

This doesn't seem to work.

Type (do not paste) this at the bottom of your column, and change the numbers as follows:

Roos,

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

Saurabh...

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

Here's an example workbook

see attached file.
Book2.xlsx

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

Select allOpen in new window

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.

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

Roy this works in your spreadsheet, but not when I copy and paste the formula into mine. Any ideas?

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.

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

Use this formula....

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

Select allOpen in new window

Saurabh...
MYFILE.xlsx

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

Select allOpen in new window