# Thread: Summing a column based on the number of the row

1. ## 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.

2. 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.png

3. MNN & Maud,

Ok here's a formula version:

R1C1 Notation:
=IF(MOD(ROW()-3,R1C3)=0,SUM(OFFSET(RC1,1-R1C3,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.

Note: Maud's revised version is located in post #2.

5. Thank you both.

The non-VBA 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.

6. The Mod function divides one number (the row #) by another number (the "every n-th 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 n-th 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

7. Fred,

Thanks for the very cogent explanation.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•