Lookup value and sum following row

[Jturek]

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

 

[Jturek]

Please keep in mind that the rows can swith location up one or two rows dependant on the amount of line items that are copied from the external prog mentioned above.

Thanks!!!

 

[Excelestial]

=SUM(offset(vlookup("Insurance",$A:$A:,1,FALSE),0,0,1,12)) ...WILL Sum 12 cells right of the location of "insurance" if the range of names is in the same column.

=SUM(offset(hlookup("Insurance",$A$1:$Z$1:,1,FALSE),0,0,12,1)) ...WILL Sum 12 cells down from the location of "insurance" if your range of names is in the same row.

 

[Excelestial]

The formula will adjust according to the location of the targeted name. Think of it as a heat-seeking missile.

 

[Jturek]

Thanks man, I appreciate the help!

For whatever reason its giving me an error when I try and paste the formula..?

 

[Aladin Akyurek]

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

Let A2:M100 house the data, with A2:A100 housing values like Insurance.

=SUM(OFFSET(INDEX($B$2:$B$100,MATCH("Insurance",$A$2:$A$100,0)),0,0,1,12))

This assumes that the item 'Insurance' occurs just once in A2:A100. Otherwise, we need a different formula.

 

[Jturek]

Awesome thanks for your help, formula comes back with "N/A".

I selected the appropriate cells as well...

ie.
=SUM(OFFSET(INDEX($E$4:$EF$67,MATCH("Insurance",$D$4:$D$67,0)),0,0,1,12))

$E$4:$EF$67 all data to the right of line items

$D$4:$D$67 column containing line items, such as Insurance

 

[Aladin Akyurek]

Awesome thanks for your help, formula comes back with "N/A".

I selected the appropriate cells as well...

ie.
=SUM(OFFSET(INDEX($E$4:$EF$67,MATCH("Insurance",$D$4:$D$67,0)),0,0,1,12))

$E$4:$EF$67 all data to the right of line items

$D$4:$D$67 column containing line items, such as Insurance

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.

 

[MrVillareal]

Hi about this one.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Expense1</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">10200</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFF00;;">Insurance</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #F79646;;">Expense3</td><td style="text-align: right;;">300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #F79646;;">Expense4</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #F79646;;">Expense5</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #F79646;;">Expense6</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #F79646;;">Expense7</td><td style="text-align: right;;">700</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #F79646;;">Expense8</td><td style="text-align: right;;">800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="background-color: #F79646;;">Expense9</td><td style="text-align: right;;">900</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #F79646;;">Expense10</td><td style="text-align: right;;">1000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #F79646;;">Expense11</td><td style="text-align: right;;">1100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="background-color: #F79646;;">Expense12</td><td style="text-align: right;;">1200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #F79646;;">Expense13</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #F79646;;">Expense14</td><td style="text-align: right;;">1400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Expense15</td><td style="text-align: right;;">1500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Expense16</td><td style="text-align: right;;">1600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Expense17</td><td style="text-align: right;;">1700</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Expense18</td><td style="text-align: right;;">1800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Expense19</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Expense20</td><td style="text-align: right;;">2000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Expense21</td><td style="text-align: right;;">2100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Expense22</td><td style="text-align: right;;">2200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Expense23</td><td style="text-align: right;;">2300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Expense24</td><td style="text-align: right;;">2400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=SUM(<font color="Blue">OFFSET(<font color="Red">B1:B24,MATCH(<font color="Green">"Insurance",A1:A24,0</font>),0,12,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Jturek]

You guys are awesome!!!!!!!

Thanks!!!!!