+ Reply to Thread
Results 1 to 12 of 12

autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

  1. 06-03-2016, 01:59 PM #1
    lonniejk
    lonniejk is offline
    Registered User
    Join Date
    06-02-2016
    Location
    nyc
    MS-Off Ver
    office 2016
    Posts
    6

    autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    Hi there,

    I'm trying to do a sum formula for sales across all products per month (each item has its own column per month, IE H3 is one product, I3 is another, etc.)

    On another sheet I'm trying to calculate total sales per month, hence I need a SUM for the above ranges. However, when I input =sum('Sheet 1'!H3:Y3) in g7 (my first cell in this column I need to generate data), and then highlight G7 and G8, and then autofill the rest down, rather than the next formula in g9 be =SUM(h8:y8) it's (h9:y9). This is incredibly frustrating and any help would be much appreciated!
    Reply With QuoteRegister To Reply
  2. 06-03-2016, 02:26 PM #2
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    This is why most people recommend using a standardized data set where the column headers are just that and the data is stacked in the rows.

    Can you post a sample workbook?
    We might be able to overcome the wonky data with some SUMIFS or INDEX formulas.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
    Reply With QuoteRegister To Reply
  3. 06-03-2016, 02:34 PM #3
    lonniejk
    lonniejk is offline
    Registered User
    Join Date
    06-02-2016
    Location
    nyc
    MS-Off Ver
    office 2016
    Posts
    6

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    Thanks Mike for your help!

    Please see attached.

    Basically on the expanded workbook, we have lists for 2015 Sales and 2016 Sales, so I'm trying to create a comparison workbook with Store 1 - 2015 on row 1 and Store 1 - 2016 on row 2, and so on.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-03-2016, 02:45 PM #4
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    So you have to make sure the store names are IDENTICAL, where your SALES sheet has Store 1 and your YoY Summary has Store #1.

    Once you change them to be identical, you can put the following formula in cell F7, then paste in f9, f11 etc.
    Please Login or Register  to view this content.
    How this works is it says within the ENTIRE range of H:Y find the row that matches the Store name (you have them as numbers but I would imagine they are names) then ONLY sum H:Y for THAT row.
    Reply With QuoteRegister To Reply
  5. 06-03-2016, 02:50 PM #5
    lonniejk
    lonniejk is offline
    Registered User
    Join Date
    06-02-2016
    Location
    nyc
    MS-Off Ver
    office 2016
    Posts
    6

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    That's the thing though is there's 1100 entries - i can't just copy them, i need a way to be able to drag the formula down. And the store names are identical, that was just an error on my part when I quickly went to create an example spreadsheet. As after this formula is complete, I'm going to have to do the same for the 2016 listing.
    Reply With QuoteRegister To Reply
  6. 06-03-2016, 02:55 PM #6
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    The formula IS complete.
    I dont understand, the formula works for the example you gave.
    I suggest you frame the problem correctly as you are just having me HALF solve your problems.

    If your example is not representative of your problem, please correct it.
    The first thing I noticed was you have 2015 and 2016 in your summary but you don't have 2016 data.... I could easily write an IF statement to change the range for 2015 or 2016 but I dont know WHERE 2016 data is housed.


    ALSO you could easily filter on ONLY the 2015 rows, and then paste the formulas ALL at once.


    I can help you come up with plenty of solutions, but only if I know the problems.
    Reply With QuoteRegister To Reply
  7. 06-03-2016, 02:56 PM #7
    lonniejk
    lonniejk is offline
    Registered User
    Join Date
    06-02-2016
    Location
    nyc
    MS-Off Ver
    office 2016
    Posts
    6

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    wait! it works! i just had issues formatting it properly.

    THANKS SO MUCH!!

    actually if there is a way to do if data for 2015 and 2016 like you mentioned, that would be so great. the format is exactly the same except the sheet is named 2016 Acct Level.
    Reply With QuoteRegister To Reply
  8. 06-03-2016, 02:57 PM #8
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    Haha.
    Tell me where the 2016 data is and I can make ONE formula for you to drag down and it does it all for you.
    Reply With QuoteRegister To Reply
  9. 06-03-2016, 03:02 PM #9
    lonniejk
    lonniejk is offline
    Registered User
    Join Date
    06-02-2016
    Location
    nyc
    MS-Off Ver
    office 2016
    Posts
    6

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    The 2016 data is under another sheet called 2016 Acct Level. It's formatted identical to the example sheet I've provided, except the sheet is named 2016 instead of 2015.
    Reply With QuoteRegister To Reply
  10. 06-03-2016, 03:04 PM #10
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help! 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 06-03-2016, 03:13 PM #11
    lonniejk
    lonniejk is offline
    Registered User
    Join Date
    06-02-2016
    Location
    nyc
    MS-Off Ver
    office 2016
    Posts
    6

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    Thank you so much! I've spent hours trying to figure this out - didn't expect 20 minutes on a forum would solve my headache!
    Reply With QuoteRegister To Reply
  12. 06-03-2016, 03:16 PM #12
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: autofill sum formula from Sheet 1 (H3:Y3) on Sheet 2, skipping every row - help!

    Haha, glad to help. Helping others is 1) charitable or something and 2) beneficial for me to keep honing skills.

    Check out http://chandoo.org/wp/ if you want to learn more about excel! Free posts and he has a few classes that are good too.
    I am NOT affiliated I just think it is a great site.

    http://chandoo.org/excel-formulas/index.shtml
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to pull data from another sheet and put it in a new sheet by skipping 1 row each time
    By hijacker83 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-02-2015, 11:22 AM
  2. [SOLVED] Autofill column,using column (skipping some cells) from another sheet
    By excellover2212 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2014, 01:09 PM
  3. [SOLVED] Autofill Row (whilst skipping every other cell), using column from another sheet
    By shadypops in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-18-2013, 03:26 PM
  4. Autofill SUM formula while skipping rows
    By sooosan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-21-2012, 05:11 PM
  5. Multisheet Formula while Skipping Rows and Autofill
    By ace_1321 in forum Excel General
    Replies: 8
    Last Post: 09-19-2012, 08:31 AM
  6. reference row on another sheet skipping zeros but not skipping li.
    By Brennan Downes in forum Excel General
    Replies: 0
    Last Post: 04-20-2005, 12:06 PM
  7. Copy row sheet-sheet skipping unqualified cells
    By StevenL in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-16-2005, 07:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1