Hlookup, offset, sum

[klopchik]

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>

 

[CyrusTheVirus]

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):

=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]

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):

=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]

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):

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

 

[klopchik]

THANK YOU!! THANK YOU!! THANK YOU!!
Perfect!

 

[CyrusTheVirus]

You're welcome.