# Hlookup, offset, sum

#### klopchik

##### New Member
Hello all! It's my first post, so please be nice

I'm trying to create a "dashboard" which looks up a value (hlookup) in the top row of the data set and returns back a sum of its "scores" from the column to the right. I tried and failed at nesting hlookup, sumproduct, and wrapping them in an offset. Help please?

 bob score mary score jean score q1 xxx 1 xxx 3 xxx 0 q2 xxx 2 xxx 3 xxx 0 q3 xxx 0 xxx 3 xxx 2 q4 xxx 7 xxx 1 xxx 3 dashboard q1+2 q3+4 bob 3 ? mary ? 4 jean 0 ?

<colgroup><col width="62" span="7" style="width:47pt"> </colgroup><tbody>
</tbody>

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### CyrusTheVirus

##### Well-known Member
Welcome to the forums.

Assuming your data set is setup like the below, then try the below formula.

B2 (copied over one column and down as many as you need):
Code:
``=IF(COLUMNS(\$B\$2:B2)=1,SUM(SUMIFS(INDEX(\$F\$2:\$K\$5,,MATCH(\$A2,\$F\$1:\$K\$1,0)+1),\$E\$2:\$E\$5,{"Q1","Q2"})),SUM(SUMIFS(INDEX(\$F\$2:\$K\$5,,MATCH(\$A2,\$F\$1:\$K\$1,0)+1),\$E\$2:\$E\$5,{"Q3","Q4"})))``

 q1+2 q3+4 Quarter bob score mary score jean score bob 3 7 q1 xxx 1 xxx 3 xxx 0 mary 6 4 q2 xxx 2 xxx 3 xxx 0 jean 0 5 q3 xxx 0 xxx 3 xxx 2 q4 xxx 7 xxx 1 xxx 3

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

#### klopchik

##### New Member
Cyrus, THANK YOU. The code of-course works perfectly in this simple example, but I'm having trouble adjusting it for the growing complexity of my data set. Let me try again, please?

I'm going to have multiple tabs of results and scores for "named" people by category. There will be 5 unchanging categories so the sum function can reference them by name (cat1, cat2) as you were doing with "Q1" and "Q2" but also sum only the scores for the "looked up" person.

 data set Category bob score mary score jean score cat1 xxx 1 xxx 3 xxx 0 cat1 xxx 2 xxx 3 xxx 0 cat1 xxx 0 xxx 3 xxx 2 cat2 xxx 7 xxx 1 xxx 3 cat2 xxx 1 xxx 3 xxx 0 cat2 xxx 2 xxx 3 xxx 0 cat2 xxx 0 xxx 3 xxx 2 cat3 xxx 7 xxx 1 xxx 3 cat4 xxx 1 xxx 3 xxx 0 cat4 xxx 0 xxx 3 xxx 2 cat5 xxx 2 xxx 3 xxx 0 cat5 xxx 7 xxx 1 xxx 3

<tbody>
</tbody>

 Category dasboard cat1 cat2 cat3 cat4 cat5 bob sum of all scores for bob for cat1 sum of all scores for bob for cat3 mary sum of all scores for mary for cat1 jean sum of all scores for jean for cat3

<tbody>
</tbody>

Welcome to the forums.

Assuming your data set is setup like the below, then try the below formula.

B2 (copied over one column and down as many as you need):
Code:
``=IF(COLUMNS(\$B\$2:B2)=1,SUM(SUMIFS(INDEX(\$F\$2:\$K\$5,,MATCH(\$A2,\$F\$1:\$K\$1,0)+1),\$E\$2:\$E\$5,{"Q1","Q2"})),SUM(SUMIFS(INDEX(\$F\$2:\$K\$5,,MATCH(\$A2,\$F\$1:\$K\$1,0)+1),\$E\$2:\$E\$5,{"Q3","Q4"})))``

 q1+2 q3+4 Quarter bob score mary score jean score bob 3 7 q1 xxx 1 xxx 3 xxx 0 mary 6 4 q2 xxx 2 xxx 3 xxx 0 jean 0 5 q3 xxx 0 xxx 3 xxx 2 q4 xxx 7 xxx 1 xxx 3

<tbody>
</tbody>

#### CyrusTheVirus

##### Well-known Member
Ok, try the below. But first make your data set a 'table', do you know how to do this? Highlight the data set, and then press 'Ctrl+T'. So, as your table expands, the below formula will automatically update. You can copy/paste the below data sets into excel and then put the below formula in cell B2 and copy down/over. Reach back out with any questions.

B2 (copied down and over):
Code:
``=SUMIFS(INDEX(Table1[#Data],,MATCH(\$A2,Table1[#Headers],0)+1),Table1[Category],B\$1)``

 dasboard cat1 cat2 cat3 cat4 cat5 Category bob score mary score2 jean score3 bob 3 10 7 1 9 cat1 xxx 1 xxx 3 xxx 0 mary 9 10 1 6 4 cat1 xxx 2 xxx 3 xxx 0 jean 2 5 3 2 3 cat1 xxx 0 xxx 3 xxx 2 cat2 xxx 7 xxx 1 xxx 3 cat2 xxx 1 xxx 3 xxx 0 cat2 xxx 2 xxx 3 xxx 0 cat2 xxx 0 xxx 3 xxx 2 cat3 xxx 7 xxx 1 xxx 3 cat4 xxx 1 xxx 3 xxx 0 cat4 xxx 0 xxx 3 xxx 2 cat5 xxx 2 xxx 3 xxx 0 cat5 xxx 7 xxx 1 xxx 3

<tbody>
</tbody>

Last edited:

#### klopchik

##### New Member
THANK YOU!! THANK YOU!! THANK YOU!!
Perfect!

You're welcome.

Replies
3
Views
689
Replies
2
Views
264
Replies
1
Views
77
Replies
0
Views
590
Replies
14
Views
451

1,136,256
Messages
5,674,657
Members
419,520
Latest member
talha_ansari

### 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?

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