Dragging Formulas

D

dawsman

Board Regular
Joined
Jul 20, 2010
Messages
55
I am looking to sum two cells on one workbook and then be able to drag the formula down, but the workbook im summing the data from goes across vertically.

So when I drag down I get:
=A1+A20
=A2+A21
=A3+A22

When what I really want is:
=A1+A20
=B1+B20
=C1+C20

Cant figure out how to do this :confused:
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi
supposing you are starting in row1 try =A1+INDEX($20:$20,,ROW()) and pull down
 
Upvote 0
Jonmo1 said:
Try

=INDEX($1:$1,ROWS($1:1))+INDEX($20:$20,ROWS($1:1))
Click to expand...

This works great, one last thing, anyway to show the cell content as blank if there is no data to display, currently it is showing £0.00 when no data is there to add up and I would like the cell blank.
 
Upvote 0
Custom-format the cells to suppress display of zeros:

£0.00;-£0.00;
 
Upvote 0
shg said:
Custom-format the cells to suppress display of zeros:

£0.00;-£0.00;
Click to expand...

I have other formulas running off these cells so suppressing the zeros still affects other columns, is there no way to return a blank?
 
Upvote 0
=iferror(1 / (1 / (index($1:$1,rows($1:1)) + index($20:$20,rows($1:1)))), "")
 
Upvote 0
Note, this is suppressing the 0 result of the addition.
This is not necessarily the same as
dawsman said:
when no data is there to add up
Click to expand...

If the 2 cells just happen to add up to 0 (2 and -2), then you'll get blank instead of the actual correct answer of 0.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Drag down formula in row an increment value by more than 1
Replies
6
Views
954
StephenCrump
S
A
  • Question
Simple Index function drag down to change the row value accordingly
2
Replies
10
Views
2K
ahme0740
A
B
  • Solved
SEARCHING ANOTHER WORKBOOK FOR CURRENT ACTIVE TEXT OFFSET AND RETURNING TEXT FROM 3 CELLS OFFSET FROM FOUND TEXT
2
Replies
11
Views
126
BAKELOVEMORE
B
R
  • Solved
IF/MATCH statement that includes the SUM
Replies
5
Views
134
Fluff
Fluff
B
  • Solved
Formula help INDEX and MATCH Please :)
2
Replies
11
Views
567
BeckyLH88
B

Forum statistics

Threads
1,215,032
Messages
6,122,770
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top