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

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):
MaxRow = ActiveSheet.UsedRange.Rows.Count
MaxCol = ActiveSheet.UsedRange.Columns.Count
<tbody> </tbody>
Dim c As Long
For c = 11 To MaxCol Step 10
Cells(MaxRow, c).NumberFormat = "General"
Cells(MaxRow, c) = "=SUM(RC[-9]:RC[-2])"
Cells(MaxRow, c).NumberFormat = "$#,##0.00;[Red]$#,##0.00"
Next c
<tbody> </tbody>
End Sub




MemberMember
One
ServiceSun
School
Sun MorningSun EveningWed
Service
Youth
Event
Special EventEnvelopOtherNotesTotalTotal
1/3/1850250100500
1/7/1865750
1/10/18251251002501000Funeral
1/17/1830
Monthly
Total
7537520095500750250100034953495

<tbody>
</tbody>
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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>


150300320250210180310420175260220310360150200
300320250210180310420175260220310360150200150
320250210180310420175260220310360150200150300
250210180310420175260220310360150200150300175
21018031042017526022031036015020015030020090835

<tbody>
</tbody>



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

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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