Results 1 to 7 of 7

20130721, 16:26 #1
 Join Date
 Jun 2005
 Posts
 390
 Thanks
 3
 Thanked 0 Times in 0 Posts
Summing a column based on the number of the row
I want to sum a column of numbers predicated on the row number. For instance, I want to sum every 12 rows. The user can put in the row number of the recurring sum.
I would like to do this without the use of VBA, mainly because no one knows it here.
I am attaching a copy of a test sample. I have the counting formula, however, I had to hard code the summing formula. If it could be automater, that would be very helpful,
Thank you.

20130721, 18:34 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,680
 Thanks
 121
 Thanked 664 Times in 605 Posts
MNN,
I know you do not want a solution using VBA, however, if no one is able to come up with one without using it, this may be your backup workaround. Disregard if someone does.
Just change the number in cell C1 to the range you want and it will automatically clear column B and sum at the intervals you specified with no intervention.
HTH,
Maud
TEST COUNTING_vba_revised.pngLast edited by Maudibe; 20130721 at 22:19. Reason: Adjusted code: RG notification

20130721, 20:56 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,480
 Thanks
 376
 Thanked 1,471 Times in 1,339 Posts
MNN & Maud,
Ok here's a formula version:
R1C1 Notation:
=IF(MOD(ROW()3,R1C3)=0,SUM(OFFSET(RC1,1R1C3,0,R1C3,1)),"")
A1 Notation:
=IF(MOD(ROW()3,$C$1)=0,SUM(OFFSET($A4,1$C$1,0,$C$1,1)),"")
Test Counting.JPG
You'll note that I have different totals than Maud. I've checked these by highlighting the associated numbers and they are correct. I looked at Maud's code but by the time I figured the formula version my brain was fried so I'll leave it to Maud to figure it out. HTH
Update: I just tried changing the interval to 12 and the VBA threw an error.Last edited by RetiredGeek; 20130721 at 21:02.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130721, 21:42 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,680
 Thanks
 121
 Thanked 664 Times in 605 Posts
Good pickup RG. Adjustment made reflect in revised version.
Note: Maud's revised version is located in post #2.Last edited by RetiredGeek; 20130722 at 08:12. Reason: Point users to revised workbook

20130722, 06:46 #5
 Join Date
 Jun 2005
 Posts
 390
 Thanks
 3
 Thanked 0 Times in 0 Posts
Thank you both.
The nonVBA is the one I have to use, however, I do not understand
1. The use of the MOD(ROW) portion of the formula, and,
2. Although I understand the concept of the OFFSET, I do not understand how it works in this IF formula.
Is it possible to explain.
Thank you.

20130722, 07:28 #6
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
The Mod function divides one number (the row #) by another number (the "every nth row" in C1) and gives the remainder. Using 6, if the remainder is NOT 0, then it's NOT time to enter a sum yet (hence the "" in the IF test). So if MOD returns 1, 2, 3, 4, or 5, you get the "".
OFFSET says how many rows/cols are to be "grouped" together. Here, you're only dealing with rows. The 1$C$1 says to take a group of rows starting from 1$C$1 rows "back" from the current row (so this number is always negative, which is allowed for OFFSET)  this is the 2nd arg of OFFSET here. Then take a group of rows numbering (the 4th arg of OFFSET) $C$1  your "every nth row" starting at that 1$C$1 rows back.
So if it's time for a sum (as determined by MOD, then take the group of rows starting 5 back of the current row and going for 6 rows forward. Hence you include the current row.
Good work RG.
Hope this helps.
Fred

20130722, 08:10 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,480
 Thanks
 376
 Thanked 1,471 Times in 1,339 Posts
Fred,
Thanks for the very cogent explanation.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs