Add every 32nd cell in a column

[shorie7]

Hello,
I have a spreadsheet where I need to total every 32nd cell at the end of the sheet.
I keep trying in say, cell M1800 this formula...

=j20+j52+j84 etc.

This would work, but I have to add like 53 additions.. And then I got the same problem in different parts of the sheet.

Does anyone know an easier way please?

 

[mumps]

Try: =SUMPRODUCT(--(MOD(J:J,32)=1),J:J)

 

[shorie7]

I did that. I'm just getting the # value! Error

 

[Fluff]

Try
=SUMPRODUCT(--(MOD(ROW(F:F),32)=1),F:F)

 

[alansidman]

Here is a VBA solution

Option Explicit


Sub Every32()
    Dim lr As Long
    Dim i As Long, x As Double
    lr = Range("J" & Rows.Count).End(xlUp).Row
    x = 0
    For i = 20 To lr
        x = x + Range("J" & i)
        i = i + 32
    Next i
    MsgBox ("total = " & x)
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Dave Patton]

=sumproduct(--(mod(row(a1:a1000),32)=0),j1:j1000)

 

[Dave Patton]

Starting at J20

consider

=J20+SUMPRODUCT(--(MOD(ROW(A20:A1200)-20,32)=0),J20:J1200)-20

 

[Dave Patton]

ignore post 7. I posted incorrect information.

try

=SUMPRODUCT(--(MOD(ROW(A20:A1200)-20,32)=0),J20:J1200)

 

[Aladin Akyurek]

hello,
i have a spreadsheet where i need to total every 32nd cell at the end of the sheet.
I keep trying in say, cell m1800 this formula...

=j20+j52+j84 etc.

This would work, but i have to add like 53 additions.. And then i got the same problem in different parts of the sheet.

Does anyone know an easier way please?

=sumproduct(j20:j400,--(mod(row(j20:j400)-row(j20),32)=0))

 

[Dave Patton]

Just a caution with Alan Sidman's VBA solution.

It appears to yield an incorrect total; it does not appear to add the cells per the request.