vlookup to return the sum of multiple columns for multiple result rows

[stevensondas]

Hi,

I am trying to develop a formula that allows me to sum the hours by resource in a spreadsheet. I have resources (people) in rows, and they appear in multiple tasks (rows) in the spreadsheet. They have hours spread across columns for the month they are forecasted to perform work in. I want to sum all the hours for a resource for each year (2021, and 2022). Any recommendations on a formula to do this is greatly appreciated. Thanks for taking a look!

hours_example.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2				2021			2022
3		Task	Resource	Oct	Nov	Dec	Jan	Feb	Mar	April
4		1	Mike	120	120	120	80	80	80	80
5		2	John	80	80	80	80	80	80	80
6		3	Tom	160	160	160	160	160	160	160
7		4	Chris	40	40	40	40	40	40	40
8		5	Mike	40	40	40	40	40	40	40
9		6	Wendy	120	120	120	160	160	160	160
10		7	John	10	10	10	10	10	10	10
11
12
13			2021	2022
14		Mike
15		John
16		Tom
17		Chris
18		Wendy
Sheet1

 

[Peter_SSs]

Welcome to the MrExcel board!

Your task would be much easier if the year was repeated in each cell across row 2. Is that a possibility for you?

21 11 27.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2				2021	2021	2021	2022	2022	2022	2022
3		Task	Resource	Oct	Nov	Dec	Jan	Feb	Mar	April
4		1	Mike	120	120	120	80	80	80	80
5		2	John	80	80	80	80	80	80	80
6		3	Tom	160	160	160	160	160	160	160
7		4	Chris	40	40	40	40	40	40	40
8		5	Mike	40	40	40	40	40	40	40
9		6	Wendy	120	120	120	160	160	160	160
10		7	John	10	10	10	10	10	10	10
11
12
13			2021	2022
14		Mike	480	480
15		John	270	360
16		Tom	480	640
17		Chris	120	160
18		Wendy	360	640
Sum

Cell Formulas
Range	Formula
C14:D18	C14	=SUMPRODUCT(($D$2:$J$2=C$13)*($C$4:$C$10=$B14)*$D$4:$J$10)

 

[Peter_SSs]

If you really want to do it without those extra values in row 2, then ...

21 11 27.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2				2021			2022
3		Task	Resource	Oct	Nov	Dec	Jan	Feb	Mar	April
4		1	Mike	120	120	120	80	80	80	80
5		2	John	80	80	80	80	80	80	80
6		3	Tom	160	160	160	160	160	160	160
7		4	Chris	40	40	40	40	40	40	40
8		5	Mike	40	40	40	40	40	40	40
9		6	Wendy	120	120	120	160	160	160	160
10		7	John	10	10	10	10	10	10	10
11
12
13			2021	2022
14		Mike	480	480
15		John	270	360
16		Tom	480	640
17		Chris	120	160
18		Wendy	360	640
Sum (2)

Cell Formulas
Range	Formula
C14:D18	C14	=LET(R,$C$4:$C$10,Y,$D$2:$J$2,S,MATCH(C$13,Y,0),SUM(INDEX(FILTER($D$4:$J$10,R=$B14),SEQUENCE(COUNTIF(R,$B14)),SEQUENCE(,IFERROR(MATCH(C$13+1,Y,0),COLUMNS(Y)+1)-S,S))))

 

[stevensondas]

Welcome to the MrExcel board!

Your task would be much easier if the year was repeated in each cell across row 2. Is that a possibility for you?

21 11 27.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2				2021	2021	2021	2022	2022	2022	2022
3		Task	Resource	Oct	Nov	Dec	Jan	Feb	Mar	April
4		1	Mike	120	120	120	80	80	80	80
5		2	John	80	80	80	80	80	80	80
6		3	Tom	160	160	160	160	160	160	160
7		4	Chris	40	40	40	40	40	40	40
8		5	Mike	40	40	40	40	40	40	40
9		6	Wendy	120	120	120	160	160	160	160
10		7	John	10	10	10	10	10	10	10
11
12
13			2021	2022
14		Mike	480	480
15		John	270	360
16		Tom	480	640
17		Chris	120	160
18		Wendy	360	640
Sum

Cell Formulas
Range	Formula
C14:D18	C14	=SUMPRODUCT(($D$2:$J$2=C$13)*($C$4:$C$10=$B14)*$D$4:$J$10)

It in fact IS a possibility for me. Thank you for the reply. Trying it now, will report back on results!

 

[stevensondas]

Welcome to the MrExcel board!

Your task would be much easier if the year was repeated in each cell across row 2. Is that a possibility for you?

21 11 27.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2				2021	2021	2021	2022	2022	2022	2022
3		Task	Resource	Oct	Nov	Dec	Jan	Feb	Mar	April
4		1	Mike	120	120	120	80	80	80	80
5		2	John	80	80	80	80	80	80	80
6		3	Tom	160	160	160	160	160	160	160
7		4	Chris	40	40	40	40	40	40	40
8		5	Mike	40	40	40	40	40	40	40
9		6	Wendy	120	120	120	160	160	160	160
10		7	John	10	10	10	10	10	10	10
11
12
13			2021	2022
14		Mike	480	480
15		John	270	360
16		Tom	480	640
17		Chris	120	160
18		Wendy	360	640
Sum

Cell Formulas
Range	Formula
C14:D18	C14	=SUMPRODUCT(($D$2:$J$2=C$13)*($C$4:$C$10=$B14)*$D$4:$J$10)

It worked! Took me some trying, as I was dealing with multiple sheets and kept mis-matching the formula. But it works great! Thanks so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.