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

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

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

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

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.

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

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

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

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.

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

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

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

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

``Please Login or Register  to view this content.``

11. ## 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!

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

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

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