Returning a value in a column offset from its criteria

[JACOBB9900]

Hello I need help summing data in a column if the range of columns falls on a certain day. See below for an example of my Gantt spreadsheet. It is divided into 8 hour days. The jobs are on the left side "Y" axis and their progress is depicted in each row. To the right of the job are the operations each job needs to process through, (i.e. ADC,IR,ABS,MSK,CR,HT) and below each operation is the total time it takes to process. I need to sum the time it takes to process each operation within that hour. As you can see I summed it all manually in the table above the Gantt chart to show how I need it. As always I appreciate all the help. Please let me know if there are any questions. Figuring this out would definitely cut back a lot of time. Thank you.

 

[KRice]

Jacob,
That's good to hear...thanks for the feedback. As for the other idea, you might consider something like this. It involves changing only the references to the original column of data...essentially
B$15:B$22 is replaced with OFFSET(B$15:B$22,0,8*($T$2-$T$1)) and that messier formula is recopied throughout your two summary tables for C/T and M/H.
The formula simply shifts the reference column by some multiple of 8, and that multiple is given by ($T$2-$T$1), the difference in days between the baseline date associated with cell B15 (and reentered in cell $T$1) and the target date that you enter in cell $T$2. This works assuming your scheduling chart holds sequential days, each day with 8 columns, and no skips (meaning every calendar day occupies 8 columns, even if blank as might be the case on weekends or holidays). If that is not the case, an alternative approach might be to use INDEX/MATCH to find the target date in a row, and then use that cell reference in conjunction with OFFSET to reference the appropriate ranges.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1																				3/5/2020		<-- date associated with indexing date
2																				3/6/2020		<-- date to summarize
3			C/T HOURS									M/H HOURS
4		HRS	1	2	3	4	5	6	7	8		1	2	3	4	5	6	7	8
5		ADC	0.20	0.00	0.00	0.00	0.00	0.00	0.00	0.00		2.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6		IR	0.10	0.30	0.50	0.20	0.40	0.10	0.30	0.20		1.00	3.00	5.00	2.00	4.00	1.00	3.00	2.00
7		ABS#	0.00	0.60	0.00	0.90	0.00	0.90	0.30	0.00		0.00	1.00	0.00	1.50	0.00	1.50	0.50	0.00
8		MCH#	0.21	0.21	0.07	0.14	0.07	0.28	0.21	0.28		1.50	1.50	0.50	1.00	0.50	2.00	1.50	2.00
9		CR	0.00	0.00	0.00	0.30	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.50	0.00	0.00	0.00	0.00
10		WLD*	0.40	0.00	0.40	0.00	0.40	0.00	0.20	0.20		1.00	0.00	1.00	0.00	1.00	0.00	0.50	0.50
11		OUT	0.00	0.00	0.00	0.00	0.10	0.00	0.00	0.00		0.00	0.00	0.00	0.00	1.00	0.00	0.00	0.00
12
13		Thursday, March 5 2020								Friday, March 6 2020								Saturday, March 7 2020
14	Jobs	1	2	3	4	5	6	7	8	1	2	3	4	5	6	7	8	1	2	3	4	5	6	7	8
15	JOB 1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ADC C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ADC C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1.3
16	JOB 2	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	CR C/T0.32 O/P20 M/H2.1	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5
17	JOB 3	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	ABS# C/T0.3 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	ABS# C/T0.3 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5
18	JOB 4	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	OUT C/T0.1 O/P20 M/H1	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.09 O/P30 M/H0.5
19	JOB 5	ADC C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ADC C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	CR C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	CR C/T0.3 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1
20	JOB 6	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	OUT C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	OUT C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS C/T0.1 O/P20 M/H1	ADC C/T0.15 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	ABS C/T0.1 O/P20 M/H1
21	JOB 7	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	ABS# C/T0.3 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	ABS# C/T0.3 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	WLD* C/T0.2 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	ABS# C/T0.3 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	OUT C/T0.1 O/P20 M/H1	MCH# C/T0.07 O/P30 M/H0.5
22	JOB 8	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	IR C/T0.1 O/P20 M/H1	IR C/T0.1 O/P20 M/H1	ABS# C/T0.3 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	MCH# C/T0.07 O/P30 M/H0.5	WLD* C/T0.2 O/P30 M/H0.5
Sheet8 (3)

Cell Formulas
Range	Formula
C5:J11	C5	=SUMPRODUCT(--(LEFT(OFFSET(B$15:B$22,0,8*($T$2-$T$1)),LEN($B5))=$B5),NUMBERVALUE(MID(OFFSET(B$15:B$22,0,8*($T$2-$T$1)),SEARCH("C/T",OFFSET(B$15:B$22,0,8*($T$2-$T$1)))+3,SEARCH("O/P",OFFSET(B$15:B$22,0,8*($T$2-$T$1)))-SEARCH("C/T",OFFSET(B$15:B$22,0,8*($T$2-$T$1)))-4)))
L5:S11	L5	=SUMPRODUCT(--(LEFT(OFFSET(B$15:B$22,0,8*($T$2-$T$1)),LEN($B5))=$B5),NUMBERVALUE(MID(OFFSET(B$15:B$22,0,8*($T$2-$T$1)),SEARCH("M/H",OFFSET(B$15:B$22,0,8*($T$2-$T$1)))+3,LEN(OFFSET(B$15:B$22,0,8*($T$2-$T$1)))-(SEARCH("M/H",OFFSET(B$15:B$22,0,8*($T$2-$T$1)))+2))))