Lookup multiple cells in a range and sum the results

[JamesBowdidge]

Hi,

I have a row of shift patterns in cells in cells d33:k33, expresses like this - "0900 - 1700"

i also have a secondtable that lists the shift pattern in column 1 and the number of hours forthat shift in column 2 (so for "0900 - 1700" you get 8, and for "0900 - 1300" you get 4...

I want to (in a single cell) have a formular that will look at the shift in each cell from d33:k33 look up the corresponding number of hours from each shift and sum the result..

So if there are only 2 shifts in the range d33:k33 of "0900 - 1700" & "0900 - 1300" the result would be 12 (8 from the first lookup and 4 from the second)

Can this be done witout stacking multiple vlookups or index/match's?

thanks

James

 

[KolGuyXcel]

2 quick questions!

1. Are the shifts showing as "0900 - 1700" or 0900 - 1700?
2. How are you selecting 8 and 4 and want excel to ignore the others? Just that the shift beginning time is the same?

 

[FormR]

I want to (in a single cell) have a formular that will look at the shift in each cell from d33:k33 look up the corresponding number of hours from each shift and sum the result.

Hi, something like this maybe:

Excel 2013/2016
	A	B	C	D	E
32	Second table
33	0900 - 1700	8	12	0900 - 1700	0900 - 1300
34	0900 - 1300	4
35	0900 - 1800	3
36	0800 - 2100	9
Sheet1

Cell Formulas
Range	Formula
C33	=SUMPRODUCT(SUMIFS(B33:B3100,A33:A3100,D33:K33))

 

[JamesBowdidge]

Hi FormR,

Total genuis... I had already tried a sumproduct an failed.. thankyou very much!

KolGuy, was replying to you when FormR solved it, thanks tho!!