For-Next, Sum every 10th cell, Help Needed

J

Jeremy4110

Board Regular
Joined
Sep 26, 2015
Messages
70
Hi,

I'm trying to help a friend create a Excel workbook so the church can track "Withdraws" and "Deposits". I have most of it completed, but I can't figure out how to "SUM" every 10th cell on the last row and give the results in the last row in the last column. Below is an example of one church member record, it is 10 columns. If another member is added, 10 more columns are added. If I had five members listed I would have a total of 52 columns. The first column lists the dates, the next 50 columns would be the donations for the five members and in the last column to want to sum up the total from the five member records.

The reason I was trying to do a For-Next loop to sum up the last row of every 10th column is because members join and members leave. I want to able to sum the total from the member record regardless of how many there are. I tried many combinations to the code below but I haven't been able to get any of them to work. Will someone please help?

Thanks,
Jeremy



Rich (BB code): 
Sub TEST()
Rich (BB code): 
[TABLE="width: 0"]
<tbody>[TR]
[TD]MaxRow  = ActiveSheet.UsedRange.Rows.Count[/TD]
[/TR]
[TR]
[TD]MaxCol = ActiveSheet.UsedRange.Columns.Count[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 74"]
<tbody>[TR="bgcolor: transparent"]
[TD]    Dim c As Long[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]    For c = 11 To MaxCol Step 10[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]        Cells(MaxRow, c).NumberFormat = "General"[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]        Cells(MaxRow, c) = "=SUM(RC[-9]:RC[-2])"[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]        Cells(MaxRow, c).NumberFormat = "$#,##0.00;[Red]$#,##0.00"[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]    Next c[/TD]
[/TR]
</tbody>[/TABLE]


End Sub​


​


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Member[/TD]
[TD]Member
One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Service[/TD]
[TD]Sun
School[/TD]
[TD]Sun Morning[/TD]
[TD]Sun Evening[/TD]
[TD]Wed
Service[/TD]
[TD]Youth
Event[/TD]
[TD]Special Event[/TD]
[TD]Envelop[/TD]
[TD]Other[/TD]
[TD]Notes[/TD]
[TD]Total[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1/3/18[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/7/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65[/TD]
[TD][/TD]
[TD]750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/10/18[/TD]
[TD]25[/TD]
[TD]125[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD]Funeral[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/17/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly
Total[/TD]
[TD]75[/TD]
[TD]375[/TD]
[TD]200[/TD]
[TD]95[/TD]
[TD]500[/TD]
[TD]750[/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]3495[/TD]
[TD]3495[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
This could be done with a worksheet formula in the last cell.
If the last cell at present is AZ5 :

=SUMPRODUCT(--(MOD((COLUMN($B5:OFFSET(AZ5,0,-1))-COLUMN($K5)),10)=0),$B5:OFFSET(AZ5,0,-1))

The formula will adjust automatically when columns are inserted or deleted.

Alternatively, if every 10th column contains the total for each member and all the column totals are numbers :

=SUM($B5:OFFSET(AZ5,0,-1))/2
 
Last edited:
Upvote 0
Hi Footoo,

I have tried several variations of your formula and have got this one to work "=SUMPRODUCT((MOD(COLUMN($B$5;$O$5),4)=1)*
($B$5;$O$5)​
)".
 
Upvote 0
Hi Footoo,

I have tried several variations of your formula and have got this one to work "=SUMPRODUCT((MOD(COLUMN($B$5;$O$5),4)=1)*($B$5;$O$5))". The problem is that it only works with a specific range one time. I need to repeat the process with a changing range every month. Is there a way to use "CELLS" ,like (MaxRow, 2) to (MaxRow, MaxCol), with this formula instead of a named range, like
($B$5;$O$5)​
?

I have a sample macro below where I use MaxRow, and MaxCol to find the most recent range to insert the formula. Is there another way besides using "CELLS" to select a changing end location to enter the formula that also needs to coincide with a changing range?


Code: 
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Sub Test()[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]     MaxRow = ActiveSheet.UsedRange.Rows.Count[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]     MaxCol = ActiveSheet.UsedRange.Columns.Count[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]     ActiveSheet.Cells(MaxRow, MaxCol + 1).Select[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]'I can manually enter the formula below  and it work[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]'     =SUMPRODUCT((MOD(COLUMN($B$5:$O$5),4)=1)*($B$5:$O$5))[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]'Neither of the below work, but I'd like to use a formula similar to the the second one, using "Cells".[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]'     Selection =SUMPRODUCT((MOD(COLUMN($B$5:$O$5),4)=1)*($B$5:$O$5)[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]'[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]'     Range("P2") =SUMPRODUCT((MOD(COLUMN(CELLS(MaxRow, 3),  CELLS(MaxRow, MaxCol - 1)),4)=1)*(CELLS(MaxRow, 3), CELLS(MaxRow, MaxCol -  1)))[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>


[TABLE="width: 1024"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]150[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]300[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]320[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]250[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]210[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]180[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]310[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]420[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]175[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]260[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]220[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]310[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]360[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]150[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]200[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]300[/TD]
[TD="bgcolor: transparent, align: right"]320[/TD]
[TD="bgcolor: transparent, align: right"]250[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent, align: right"]180[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]420[/TD]
[TD="bgcolor: transparent, align: right"]175[/TD]
[TD="bgcolor: transparent, align: right"]260[/TD]
[TD="bgcolor: transparent, align: right"]220[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]360[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]320[/TD]
[TD="bgcolor: transparent, align: right"]250[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent, align: right"]180[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]420[/TD]
[TD="bgcolor: transparent, align: right"]175[/TD]
[TD="bgcolor: transparent, align: right"]260[/TD]
[TD="bgcolor: transparent, align: right"]220[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]360[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]300[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]250[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent, align: right"]180[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]420[/TD]
[TD="bgcolor: transparent, align: right"]175[/TD]
[TD="bgcolor: transparent, align: right"]260[/TD]
[TD="bgcolor: transparent, align: right"]220[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]360[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]300[/TD]
[TD="bgcolor: transparent, align: right"]175[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent, align: right"]180[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="bgcolor: transparent, align: right"]420[/TD]
[TD="align: right"]175[/TD]
[TD="bgcolor: transparent, align: right"]260[/TD]
[TD="bgcolor: transparent, align: right"]220[/TD]
[TD="bgcolor: transparent, align: right"]310[/TD]
[TD="align: right"]360[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="align: right"]300[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent, align: right"]90[/TD]
[TD="bgcolor: transparent, align: right"]835[/TD]
[/TR]
</tbody>[/TABLE]



Thanks,
Jeremy​
 
Last edited:
Upvote 0
For the formulas to continue working after columns are inserted/deleted, they need to include the OFFSET function.

If your data is like in your first post - with a subtotal every 10th column - then this would be the simplest (entered in AZ5) :
=SUM(OFFSET($A5,0,1):OFFSET(AZ5,0,-1))/2

If you prefer doing it by macro instead of a formula :
Code: 
Sub Test()
Dim c As Long, MaxRow&, MaxCol&
MaxRow = ActiveSheet.UsedRange.Rows.Count
MaxCol = ActiveSheet.UsedRange.Columns.Count
For c = 11 To MaxCol Step 10
    Cells(MaxRow, c).FormulaR1C1 = "=SUM(RC[-9]:RC[-2])"
    Cells(MaxRow, c).NumberFormat = "$#,##0.00;[Red]$#,##0.00"
Next c
[COLOR=#ff0000]Cells(MaxRow, MaxCol) = Evaluate("SUM(B" & MaxRow & ":" & Cells(MaxRow, MaxCol - 1).Address & ")/2")
Cells(MaxRow, MaxCol).NumberFormat = "$#,##0.00;[Red]$#,##0.00"[/COLOR]
End Sub
 
Upvote 0
Hi Footoo,

Thank you so much for your help, the macro is exactly what I needed because the variables "MaxRow and "MaxCol" are subject to change. I also want to apologize for delay in my response. I have had some health issues that have slowed me down, more on some days than others.

Anyway, I greatly appreciate your help, you and other have been so awesome with your suggestions, recommendation and just plain outright showing me how it's done.

Thanks,
Jeremy
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Every 10 dates should count and sum end of every 10th date
Replies
8
Views
385
vishu
V
ExcelNewbie2020
  • Solved
sum function that will reset the running total every after 5 counts
Replies
3
Views
283
Cubist
Cubist
U
  • Solved
Help with Index and multiple matches that include sum
Replies
2
Views
336
usnapoleon
U
L
  • Question Question
Send cell values to 2 other sheets according to condition
Replies
2
Views
196
leobrice
L
A
  • Solved
Autonumbering in cell based on last row in column
Replies
2
Views
70
abdo meghari
A

Forum statistics

Threads
1,223,712
Messages
6,174,031
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top