hLookup

[Chris3177]

I want excel to look up a certain value in a row and for each cell that has the value I want it to sum the corresponding numerical values.

For example:
C/R A B C D E F
1 Director CRA Mng CRA CRA Mng
2
3
4
5
6 1.4 1.3 1.0 .08 .98 .78

The formula should look up "CRA" in row 1 and then sum all of the corresponding values in row 6 i.e. 1.3+.08+.98 = 2.36

I tried a hlookup, but don't seem to know how to use the formula to look for multiple values of the same and then add them together???

Thank you!

 

[Chris3177]

Well, at this point I'm confused and would need to see the setup with the data.

Can you post a sample file? You can use a free file host if need be.

How does one "use a free file host?" I am more than happy to share the file, just not sure how to share it.

 

[T. Valko]

How does one "use a free file host?" I am more than happy to share the file, just not sure how to share it.

There are websites that will allow you to upload a file (store the file on their servers) for FREE so that you can share the file with others.

Some sites have file size and/or file type restrictions.

Sometimes I use this site but there are many others.

CJoint

http://tinyurl.com/24xfnt

It's a French site that gets translated to English through Google.

You upload the file and they will give you a link to the file where others can download it. Then you'd post that link in your reply here.

 

[Chris3177]

There are websites that will allow you to upload a file (store the file on their servers) for FREE so that you can share the file with others.

Some sites have file size and/or file type restrictions.

Sometimes I use this site but there are many others.

CJoint

http://tinyurl.com/24xfnt

It's a French site that gets translated to English through Google.

You upload the file and they will give you a link to the file where others can download it. Then you'd post that link in your reply here.

Attached is the link. Please refer to cell E61 as this is where I am trying to put the formula:

http://cjoint.com/?3HkwT76HZQJ

 

[T. Valko]

Attached is the link. Please refer to cell E61 as this is where I am trying to put the formula:

http://cjoint.com/?3HkwT76HZQJ

OK, I have your file in front of me and I'm still confused about where all this data is supposed to be.

I do see why the formula won't work. You have merged cells on the criteria row and the sum range cells are offset.

None of the criteria cells contain just CRA. There are 3 cells that contain CRA as a substring:

TBD-CRA III (U.S.)
Isabella/CRA
TBD/CRA

So, where exactly are the cells to be summed? You say the cells in gray but there are red, gray and yellow cells all over the place!

 

[Chris3177]

OK, I have your file in front of me and I'm still confused about where all this data is supposed to be.

I do see why the formula won't work. You have merged cells on the criteria row and the sum range cells are offset.

None of the criteria cells contain just CRA. There are 3 cells that contain CRA as a substring:

TBD-CRA III (U.S.)
Isabella/CRA
TBD/CRA

So, where exactly are the cells to be summed? You say the cells in gray but there are red, gray and yellow cells all over the place!

I want the formula to look at row 6 and every it the criteria is true, then sum the value in the corresponding section below for the column labeled T(%).

For example, if we look at a subsection of the sheet for simplicity purposes D6:W10.

If the formula was written to sum the T(%) values every time if found "Mng" for "Test 1" it would show a value of "0.00". Which is the sum of R10+W10.

I hope this helps.

Thank you!

 

[markmzz]

Chris3177,

Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">60</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">CRA</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">CDC</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Prg Dir BS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">DM</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">BS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">SAS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">PD</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Stat</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">CIRCULAR</td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style="text-align: right;;">3.425</td><td style="text-align: right;;">1.730</td><td style="text-align: right;;">0.000</td><td style="text-align: right;;">0.875</td><td style="text-align: right;;">0.000</td><td style="text-align: right;;">0.000</td><td style="text-align: right;;">4.735</td><td style="text-align: right;;">1.520</td><td style="text-align: right;;">1.360</td></tr><tr ><td style="color: #161120;text-align: center;">62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">63</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td></tr></tbody></table><p style="width:10.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Resources updated</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>Array 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">E61</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,OFFSET(<font color="Green">$D10:$GK12,0,4</font>)</font>)</font>)+SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,OFFSET(<font color="Green">$D14:$GK21,0,4</font>)</font>)</font>)+SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,OFFSET(<font color="Green">$D23:$GK26,0,4</font>)</font>)</font>)+SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,OFFSET(<font color="Green">$D33:$GK42,0,4</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[T. Valko]

I want the formula to look at row 6 and every it the criteria is true, then sum the value in the corresponding section below for the column labeled T(%).

For example, if we look at a subsection of the sheet for simplicity purposes D6:W10.

If the formula was written to sum the T(%) values every time if found "Mng" for "Test 1" it would show a value of "0.00". Which is the sum of R10+W10.

I hope this helps.

Thank you!

OK, in the file you have this note:

This cell should look at row D7:GK7 and lookup "CRA" and when it finds "CRA" in that row, sum the corresponding values in the gray cells.

Try this...

=SUMIF(D6:GK6,"Mng",H10:GK10)

 

[markmzz]

Or maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">60</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">CRA</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">CDC</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Prg Dir BS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">DM</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">BS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">SAS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">PD</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Stat</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">CIRCULAR</td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style="text-align: right;;">3.425</td><td style="text-align: right;;">1.730</td><td style="text-align: right;;">0.000</td><td style="text-align: right;;">0.875</td><td style="text-align: right;;">0.000</td><td style="text-align: right;;">0.000</td><td style="text-align: right;;">4.735</td><td style="text-align: right;;">1.520</td><td style="text-align: right;;">1.360</td></tr><tr ><td style="color: #161120;text-align: center;">62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">63</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td><td style=";">*********</td></tr></tbody></table><p style="width:10.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Resources_Updated</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>Array 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">E61</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,$H10:$GK12</font>)</font>)+SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,$H14:$GK21</font>)</font>)+SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,$H23:$GK26</font>)</font>)+SUM(<font color="Blue">IF(<font color="Red">$D6:$GK6=E60,$H33:$GK42</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[markmzz]

Another way (a little small - array formula - use Ctrl+Shift+Enter and not only Enter):

=SUM(($D6:$GG6=E60)*$H10:$GK12)+SUM(($D6:$GG6=E60)*$H14:$GK21)+SUM(($D6:$GG6=E60)*$H23:$GK26)+SUM(($D6:$GG6=E60)*$H33:$GK42)

Markmzz

 

[Chris3177]

I am not sure how the array formula works, but it seems to be meeting my needs at this time. Depending, I may need to come back with a follow up question, but at this formula is working well!

As always, thank you for the help!

Chris