Lookup value and sum following row

Jturek

New Member
Joined
Aug 19, 2011
Messages
10
Hello, was wondering if someone could help me with the following:

I have a spreadsheet that has a variable amount of line items (statement of cash flows) that I copy from an external program.

I wanted to use look up to find a particular value (ie. "Insurance") and sum the 12 cells following that value.

Any help would be greatly appreciated!

Thanks!!!!!!!!!!!!!!!!!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Is there any way to edit the formula so if a line item doesn't appear, for example "Base Rent Abatements", it will automatically fill the destination cell with a zero or "-"?

Here's what I have edited the formula down to:

=SUM(OFFSET(INDEX(E4:E65,MATCH("Base Rent Abatements",$D$4:$D$65,0)),0,0,1,1))

Thanks!
 
Upvote 0
Sure! Just preface the formula with a condition to trap the error (caused by the absence of "Base Rate Abatements"...replace the error with a zero...and if the words are there, then sum as needed.

=IF(ISERROR(SUM(OFFSET(B1:B24,MATCH("Base Rent Abatements",A1:A24,0),0, 12,1)),"0",SUM(OFFSET(B1:B24,MATCH("Base Rent Abatements",A1:A24,0),0, 12,1))
 
Upvote 0
Is there any way to edit the formula so if a line item doesn't appear, for example "Base Rent Abatements", it will automatically fill the destination cell with a zero or "-"?

Here's what I have edited the formula down to:

=SUM(OFFSET(INDEX(E4:E65,MATCH("Base Rent Abatements",$D$4:$D$65,0)),0,0,1,1))

Thanks!
What version of Excel are you using?
 
Upvote 0
One way...

=IFERROR(SUM(OFFSET(INDEX(E4:E65,MATCH("Base Rent Abatements",$D$4:$D$65,0)),0,0,1,1)),0)
I just noticed that the parameters of the OFFSET function are returning just a single result so you don't need the SUM function in that case.

=IFERROR(OFFSET(INDEX(E4:E65,MATCH("Base Rent Abatements",$D$4:$D$65,0)),0,0,1,1),0)
 
Upvote 0
Insurance must exist/occur in D4:D67. Check the cell in D4:D67 housing 'Insurance" for extranous spaces or other invisible chars around the entry.

When pasting a new data sheet, the destination cell for "Insurance" yields a result of "N/A".

While the formula in the destination cell is correct, it only works when I delete and re-type over the reference cell ("Insurance").

Any easy ways to clean up the copied data sheet for extraneous spaces or other invisible chars so this extra step isn't necc..?

Thanks again
 
Upvote 0
When pasting a new data sheet, the destination cell for "Insurance" yields a result of "N/A".

While the formula in the destination cell is correct, it only works when I delete and re-type over the reference cell ("Insurance").

Any easy ways to clean up the copied data sheet for extraneous spaces or other invisible chars so this extra step isn't necc..?

Thanks again

Try to run the trimall macro on your data. The code for this macro is available at:

Either...

http://www.mrexcel.com/forum/showthread.php?t=69341&highlight=trimall&page=2 (Post #13)

Or...

http://dmcritchie.mvps.org/excel/join.htm
 
Upvote 0

Forum statistics

Threads
1,225,156
Messages
6,183,227
Members
453,152
Latest member
ChrisMd

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