Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need Help with SUM function
I have a software program that kicks out Excel reports with data from its fields and I need to add the cells up. I can put the =SUM(A1:D1) formula in a field within the software so it adds up A1 through D1 no problem but the simple =SUM(A1:D1) then shows up in row 2. So where it should be =SUM(A2:D2) Excel is taking the literall text in the field so it is not working beyond row 1. I get =SUM(A1:D1) in all rows. My thinking is to come up with a formula that uses the ROW() function to tell the next cell down to use the proper formula so I'm trying =SUM(A(ROW()):D(ROW())) and it is not working. I think I'm on the right track but don't quite know the syntacs. Suggestions? Thanks. -- spackler ------------------------------------------------------------------------ spackler's Profile: http://www.excelforum.com/member.php...o&userid=26212 View this thread: http://www.excelforum.com/showthread...hreadid=395191 |
#2
|
|||
|
|||
=SUM(INDIRECT("A"&ROW()&":D"&ROW()))
-- Vasant "spackler" wrote in message ... I have a software program that kicks out Excel reports with data from its fields and I need to add the cells up. I can put the =SUM(A1:D1) formula in a field within the software so it adds up A1 through D1 no problem but the simple =SUM(A1:D1) then shows up in row 2. So where it should be =SUM(A2:D2) Excel is taking the literall text in the field so it is not working beyond row 1. I get =SUM(A1:D1) in all rows. My thinking is to come up with a formula that uses the ROW() function to tell the next cell down to use the proper formula so I'm trying =SUM(A(ROW()):D(ROW())) and it is not working. I think I'm on the right track but don't quite know the syntacs. Suggestions? Thanks. -- spackler ------------------------------------------------------------------------ spackler's Profile: http://www.excelforum.com/member.php...o&userid=26212 View this thread: http://www.excelforum.com/showthread...hreadid=395191 |
#3
|
|||
|
|||
Great. Awesome. Perfect. Thanks Vasant! Now I'm trying to do the same thing with =I2-P2 and =D2/A2 and having no luck adapting what you gave me above for the SUM. For the subtraction I was trying : =SUM(INDIRECT("I"&ROW()&"-P"&ROW())) or =(INDIRECT("I"&ROW()&"-P"&ROW())) with no luck. I have not tried the division yet as I realize my function understanding is lacking... Suggestions? Thanks. -- spackler ------------------------------------------------------------------------ spackler's Profile: http://www.excelforum.com/member.php...o&userid=26212 View this thread: http://www.excelforum.com/showthread...hreadid=395191 |
#4
|
|||
|
|||
INDIRECT is not really intuitive (at least not to me). It usually takes me a
bit of trial=and-error. Try: =INDIRECT("I"&ROW())-INDIRECT("P"&ROW()) and: =INDIRECT("D"&ROW())/INDIRECT("A"&ROW()) -- Vasant "spackler" wrote in message ... Great. Awesome. Perfect. Thanks Vasant! Now I'm trying to do the same thing with =I2-P2 and =D2/A2 and having no luck adapting what you gave me above for the SUM. For the subtraction I was trying : =SUM(INDIRECT("I"&ROW()&"-P"&ROW())) or =(INDIRECT("I"&ROW()&"-P"&ROW())) with no luck. I have not tried the division yet as I realize my function understanding is lacking... Suggestions? Thanks. -- spackler ------------------------------------------------------------------------ spackler's Profile: http://www.excelforum.com/member.php...o&userid=26212 View this thread: http://www.excelforum.com/showthread...hreadid=395191 |
#5
|
|||
|
|||
Those look to be working, thanks again! -- spackler ------------------------------------------------------------------------ spackler's Profile: http://www.excelforum.com/member.php...o&userid=26212 View this thread: http://www.excelforum.com/showthread...hreadid=395191 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |