+ Reply to Thread
Results 1 to 3 of 3

SUM formula that counts X additional cells for every new row it's copied down to

  1. #1
    Registered User
    Join Date
    07-09-2019
    Location
    SF, CA
    MS-Off Ver
    1906
    Posts
    5

    SUM formula that counts X additional cells for every new row it's copied down to

    I know the title is a mouthful, let me try to explain:

    I have a sheet that looks as follows (this is a condensed version):
    1 2 3 4
    1 2 3 4
    1 2 3 4 5 6
    1 2 3 4 5 6 7 8

    What I would like to do is create a column that SUMs the first half of each row. Assuming the rows in the table above are labeled A, B, C, D (I couldn't figure out how to do that in HTML), I would want the cell in the first row of this new column to be = SUM(B1:B2), the next row to be =SUM(C1:C3) and the next one to be =SUM(D1:D4). I would like to be able to drag this formula down, as the actual table has about 100 rows. How could this best be done? I hope I articulated my question well.

    Thank you

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: SUM formula that counts X additional cells for every new row it's copied down to

    Okay
    1. Will it ever be an odd number? and if so, do you add 1 or subtract 1 (i.e. with 9 values, will it be 4 or 5)?
    Assuming starting in Sheet1!A1 to a max count in Z1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula errs on the side of caution (i.e. for 9 values, sums 4)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    07-09-2019
    Location
    SF, CA
    MS-Off Ver
    1906
    Posts
    5

    Re: SUM formula that counts X additional cells for every new row it's copied down to

    Thanks for your response.

    This doesn't seem to be working for me. It shows me a sum of the total row. I have copied in the exact formula.

    Does this simply take the sum of the whole row and divide it by 2? I would like just the sum of the first half, depending on the number of columns that are populated; this will vary and will not always be equal to half of the total row sum.

    EDIT: I seem to have inputted my own numbers wrong. I am looking more into it now. Initially it is working. Thanks!

    EDIT2: This worked great. I tweaked it a bit based on the actual spreadsheet but it ended up doing exactly what I needed it to. Thank you!!!
    Last edited by ttt123ttt; 08-06-2019 at 05:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 08-09-2018, 04:43 AM
  2. Replies: 7
    Last Post: 01-10-2017, 12:49 PM
  3. [SOLVED] Modfication of Existing Code for additional additional cells
    By thilag in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-29-2016, 03:00 AM
  4. Formula that counts whether two cells in a row contain data.
    By hotdogbuns in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-23-2015, 04:37 PM
  5. [SOLVED] I need to add additional criteria to an array Sumif that counts individual values
    By dc0822 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-20-2015, 04:24 PM
  6. [SOLVED] Average Formula that counts blank cells
    By ChrisHallas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2012, 08:20 AM
  7. Add additional text within copied sheet name brackets
    By DeaJL in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-16-2008, 10:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1