# Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

1. ## Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Hi,

I need some help with copying a formula which adds columns from another Sheet.

I have 2x B2B and 1x B2C.

I would like to add B2B sales togetherr in first column and then =sales 3 on second. I want to repeat this formula by dragging it to the right and not type formula in each cell by myself.

#Sheet 1
A B C D E F G
1 Period 1 2
2 Sales B2B B2B B2C B2B B2B B2C.. n1+
3 Values 1 2 3 4 5 6

#Sheet2 - Dasboard
A B C D E F G
1 Period 1 2
2 B2B =Sheet1!B3+Sheet1!C3 (WHEN I TRY TO COPY THIS FURTHER IT JUST ADDS 1, but what I had preferred is that it took next B2B sales from Period 2 etc: =Sheet1!E3+Sheet1!F3)
3 B2C =Sheet1!D3

I tried to explain it as easy as possible.

Thanks in advance for usefull tips!

- Hens  Register To Reply

2. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.  Register To Reply

3. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

I have now enclosed an example, with comment.

- Hens  Register To Reply

4. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

in b4 =SUM(OFFSET('Inndata-Sheet1'!\$B5,0,(COLUMN(A1)-1)*1.5,1,2))
in c4 ==SUM(OFFSET('Inndata-Sheet1'!\$D5,0,(COLUMN(A1)-1)*1.5,))  Register To Reply

5. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Thanks for fast respond.

Enclosed is a new version of the dashboard - which may be of better use.
Do you have similar formulas for that view? Same INNDATA follows. Same principle of just copying formulas to next periods.

- Hens  Register To Reply

6. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula Originally Posted by hens91 Thanks for fast respond.

Enclosed is a new version of the dashboard - which may be of better use.
Do you have similar formulas for that view? Same INNDATA follows. Same principle of just copying formulas to next periods.

- Hens
I would love to get an answer for this post - if anyone might have it.

-Hens  Register To Reply

7. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Try in B4:

=SUM(OFFSET('Inndata-Sheet1'!\$A5,0,MATCH(B\$2,'Inndata-Sheet1'!\$B\$3:\$S\$3,0),1,2))  Register To Reply

8. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

you just need to think how much you need to move for each column, before to move 2 columns you needed to move 3 columns on the other sheet, hence *1.5, this time you need to move 3 columns for every 1 column. The formula are the same with this changes
B5 =SUM(OFFSET('Inndata-Sheet1'!\$B5,0,(COLUMN(A1)-1)*3,1,2))
b6 =OFFSET('Inndata-Sheet1'!\$D5,0,(COLUMN(A1)-1)*3)
B9 =SUM(OFFSET('Inndata-Sheet1'!\$B6,0,(COLUMN(A5)-1)*3,1,2))
B10 =OFFSET('Inndata-Sheet1'!\$D6,0,(COLUMN(A5)-1)*3)

its worth your time working out what the formula do
column is just a number A=1 b=2 c=3 etc
offset(starting cell, rows to move, columns to move,height, width)  Register To Reply

9. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula Originally Posted by davsth you just need to think how much you need to move for each column, before to move 2 columns you needed to move 3 columns on the other sheet, hence *1.5, this time you need to move 3 columns for every 1 column. The formula are the same with this changes
B5 =SUM(OFFSET('Inndata-Sheet1'!\$B5,0,(COLUMN(A1)-1)*3,1,2))
b6 =OFFSET('Inndata-Sheet1'!\$D5,0,(COLUMN(A1)-1)*3)
B9 =SUM(OFFSET('Inndata-Sheet1'!\$B6,0,(COLUMN(A5)-1)*3,1,2))
B10 =OFFSET('Inndata-Sheet1'!\$D6,0,(COLUMN(A5)-1)*3)

its worth your time working out what the formula do
column is just a number A=1 b=2 c=3 etc
offset(starting cell, rows to move, columns to move,height, width)
Thanks a lot for the answer. Works perfect in test-sheet. I will sit down trying to write this in my actual sheet to understand the formula better.
Again, thanks!  Register To Reply