Hlookup, offset, sum

klopchik

New Member
Joined
May 3, 2019
Messages
3
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?

bobscoremaryscorejeanscore
q1xxx1xxx3xxx0
q2xxx2xxx3xxx0
q3xxx0xxx3xxx2
q4xxx7xxx1xxx3
dashboard
q1+2q3+4
bob3?
mary?4
jean0?

<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
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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+2q3+4Quarterbobscoremaryscorejeanscore
bob37q1xxx1xxx3xxx0
mary64q2xxx2xxx3xxx0
jean05q3xxx0xxx3xxx2
q4xxx7xxx1xxx3

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

klopchik

New Member
Joined
May 3, 2019
Messages
3
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
Categorybobscoremaryscorejeanscore
cat1xxx1xxx3xxx0
cat1xxx2xxx3xxx0
cat1xxx0xxx3xxx2
cat2xxx7xxx1xxx3
cat2xxx1xxx3xxx0
cat2xxx2xxx3xxx0
cat2xxx0xxx3xxx2
cat3xxx7xxx1xxx3
cat4xxx1xxx3xxx0
cat4xxx0xxx3xxx2
cat5xxx2xxx3xxx0
cat5xxx7xxx1xxx3

<tbody>
</tbody>



Category
dasboardcat1cat2cat3cat4cat5
bobsum of all scores for bob for cat1sum of all scores for bob for cat3
marysum of all scores for mary for cat1
jeansum 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+2q3+4Quarterbobscoremaryscorejeanscore
bob37q1xxx1xxx3xxx0
mary64q2xxx2xxx3xxx0
jean05q3xxx0xxx3xxx2
q4xxx7xxx1xxx3

<tbody>
</tbody>
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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)


dasboardcat1cat2cat3cat4cat5Categorybobscoremaryscore2jeanscore3
bob310719cat1xxx1xxx3xxx0
mary910164cat1xxx2xxx3xxx0
jean25323cat1xxx0xxx3xxx2
cat2xxx7xxx1xxx3
cat2xxx1xxx3xxx0
cat2xxx2xxx3xxx0
cat2xxx0xxx3xxx2
cat3xxx7xxx1xxx3
cat4xxx1xxx3xxx0
cat4xxx0xxx3xxx2
cat5xxx2xxx3xxx0
cat5xxx7xxx1xxx3

<tbody>
</tbody>
 
Last edited:

Forum statistics

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

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
Top