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>
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
365
Platform
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
736
Office Version
365
Platform
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,081,676
Messages
5,360,441
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top