Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    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.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 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.png
    Attached Files Attached Files
    Last edited by Maudibe; 2013-07-21 at 22:19. Reason: Adjusted code: RG notification

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2013-07-21 at 21:02.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Good pickup RG. Adjustment made reflect in revised version.

    Note: Maud's revised version is located in post #2.
    Last edited by RetiredGeek; 2013-07-22 at 08:12. Reason: Point users to revised workbook

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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. #6
    Silver Lounger
    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 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. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

Posting Permissions

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