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.

 

[JACOBB9900]

I forgot to mention I need it to sum the data for the current day.

 

[KRice]

Here is an idea to consider. Since the time associated with a particular process is placed below the process code, you can make use of shifted arrays...one array identifies instances of a specific process in the column and the shifted array ensures that the correct time is accounted for.

MrExcel_2.xlsm
	I	J	K	L	M	N	O	P	Q	R
1		Tuesday
2	Hrs	1	2	3	4	5	6	7	8	Process daily sum
3	ADC	0.7	2.1	0	0.7	0.7	0	0	0.7	4.9
4	IR	1	1	2.5	2	2	1	1	0.5	11
5	ABS	0.5	0.25	0.25	0.25	0.5	1	0.75	0.25	3.75
6	MSK	0.6	0.9	0.9	0.45	0.45	0.45	0.9	1.8	6.45
7	CR	0.6	1.2	1.2	2.4	2.4	3	3	3.6	17.4
8	HT	0	0	0	0	0	0	0	0	0
9	Hourly Sum	3.4	5.45	4.85	5.8	6.05	5.45	5.65	6.85	43.5
10		Tuesday
11	Hours	1	2	3	4	5	6	7	8
12	JOB 1	CR	CR	CR	CR	CR	CR	CR	CR
13	Process t	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6
14	JOB 2	MSK	CR	CR	CR	CR	CR	CR	CR
15	Process t	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6
16	JOB 3	ABS	MSK	MSK	CR	CR	CR	CR	CR
17	Process t	0.25	0.45	0.45	0.6	0.6	0.6	0.6	0.6
18	JOB 4	ABS	MSK	MSK	CR	CR	CR	CR	CR
19	Process t	0.25	0.45	0.45	0.6	0.6	0.6	0.6	0.6
20	JOB 5	IR	ABS	ABS	MSK	MSK	CR	CR	CR
21	Process t	0.5	0.25	0.25	0.45	0.45	0.6	0.6	0.6
22	JOB 6	IR	IR	IR	ABS	ABS	MSK	MSK	CR
23	Process t	0.5	0.5	0.5	0.25	0.25	0.45	0.45	0.6
24	JOB 7	ADC	IR	IR	IR	ABS	ABS	MSK	MSK
25	Process t	0.7	0.5	0.5	0.5	0.25	0.25	0.45	0.45
26	JOB 8		ADC	IR	IR	IR	ABS	ABS	MSK
27	Process t		0.7	0.5	0.5	0.5	0.25	0.25	0.45
28	JOB 9		ADC	IR	IR	IR	ABS	ABS	MSK
29	Process t		0.7	0.5	0.5	0.5	0.25	0.25	0.45
30	JOB 10		ADC	IR	IR	IR	ABS	ABS	MSK
31	Process t		0.7	0.5	0.5	0.5	0.25	0.25	0.45
32	JOB 11				ADC	IR	IR	IR	ABS
33	Process t				0.7	0.5	0.5	0.5	0.25
34	JOB 12					ADC	IR	IR	IR
35	Process t					0.7	0.5	0.5	0.5
36	JOB 13								ADC
37	Process t								0.7
Sheet8

Cell Formulas
Range	Formula
J1,J2:Q2	J1	=J10
J3:Q3	J3	=SUMPRODUCT(--(J$12:J$37="adc"),J$13:J$38)
R3:R8	R3	=SUM(J3:Q3)
J4:Q4	J4	=SUMPRODUCT(--(J$12:J$37="IR"),J$13:J$38)
J5:Q5	J5	=SUMPRODUCT(--(J$12:J$37="ABS"),J$13:J$38)
J6:Q6	J6	=SUMPRODUCT(--(J$12:J$37="MSK"),J$13:J$38)
J7:Q7	J7	=SUMPRODUCT(--(J$12:J$37="CR"),J$13:J$38)
J8:Q8	J8	=SUMPRODUCT(--(J$12:J$37="HT"),J$13:J$38)
J9:R9	J9	=SUM(J3:J8)

 

[KRice]

So if your list of processes are found in some column (in this case column i), a single formula can be inserted into the upper left table cell and copied throughout the table to populate all eight hours.
=SUMPRODUCT(--(J$12:J$37=$I3),J$13:J$38)

 

[JACOBB9900]

KRice thank you so much! This is what i needed! Would you have any idea for a formula that would update the data on a daily basis? In the example above if the current day were to change to Wednesday i would need for the process times to be summed in the the columns that fall under Wednesday.

I really appreciate the help.
Thank you again.

 

[KRice]

Yes, the SUMPRODUCT formula can be adapted to the layout of your worksheet. A few questions:

1. When you mentioned a need to sum the data for each day, does that mean to sum across all processes hour-by-hour (as in row 9 in my earlier post), or to sum each process across all hours (as in column R), or do you want a global sum for that day (as in cell R9)? I ask because the choice may affect your table structure, and it might be odd to have a summation column between each day.
2. Do you anticipate having a dedicated number of rows for this schedule, or could it grow in ways that are not well understood? I ask because some that may influence how to go about finding the lower end of the range specified in the formula.
3. Do you anticipate having anything else below the schedule table? Because if we settle on a large range of cells, and something unrelated is placed in the range, the results won't be correct.

 

[KRice]

Here is an example where you can manually set the length of the array using the value in cell A11 (by declaring the maximum number of processes in the schedule). This could be automatically determined if necessary. In any case, the formula in B3 can be copied throughout the summary table, across all days. The formula uses the hours (in a day), row 12 here, as an indexing location and uses the OFFSET formula to establish the ranges necessary for the calculation. The hourly totals are below the summary table, and the total daily sum (Dsum) is shown in the upper right corner of the daily summary block.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Tuesday						Dsum ->	43.5	Wednesday						Dsum ->	47.23
2	Hrs	1	2	3	4	5	6	7	8	1	2	3	4	5	6	7	8
3	ADC	0.7	2.1	0	0.7	0.7	0	0	0.7	0.7	2.1	0	0.7	1.4	0	0.7	0.7
4	IR	1	1	2.5	2	2	1	1	0.5	1	1	2.5	2	2	1.5	1.5	1.5
5	ABS	0.5	0.25	0.25	0.25	0.5	1	0.75	0.25	0.5	0.25	0.25	0.25	0.5	1	0.75	0.25
6	MSK	0.6	0.9	0.9	0.45	0.45	0.45	0.9	1.8	0.6	0.9	0.9	0.45	0.45	0.45	0.9	1.8
7	CR	0.6	1.2	1.2	2.4	2.4	3	3	3.6	0.6	1.2	1.2	2.4	2.4	3	3	3.6
8	HT	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.33
9	Hourly Sum	3.4	5.45	4.85	5.8	6.05	5.45	5.65	6.85	3.4	5.45	4.85	5.8	6.75	5.95	6.85	8.18
10	â MaxProc
11	16	Tuesday								Wednesday
12	Hours	1	2	3	4	5	6	7	8	1	2	3	4	5	6	7	8
13	JOB 1	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR	CR
14	Process t	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6
15	JOB 2	MSK	CR	CR	CR	CR	CR	CR	CR	MSK	CR	CR	CR	CR	CR	CR	CR
16	Process t	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6	0.6
17	JOB 3	ABS	MSK	MSK	CR	CR	CR	CR	CR	ABS	MSK	MSK	CR	CR	CR	CR	CR
18	Process t	0.25	0.45	0.45	0.6	0.6	0.6	0.6	0.6	0.25	0.45	0.45	0.6	0.6	0.6	0.6	0.6
19	JOB 4	ABS	MSK	MSK	CR	CR	CR	CR	CR	ABS	MSK	MSK	CR	CR	CR	CR	CR
20	Process t	0.25	0.45	0.45	0.6	0.6	0.6	0.6	0.6	0.25	0.45	0.45	0.6	0.6	0.6	0.6	0.6
21	JOB 5	IR	ABS	ABS	MSK	MSK	CR	CR	CR	IR	ABS	ABS	MSK	MSK	CR	CR	CR
22	Process t	0.5	0.25	0.25	0.45	0.45	0.6	0.6	0.6	0.5	0.25	0.25	0.45	0.45	0.6	0.6	0.6
23	JOB 6	IR	IR	IR	ABS	ABS	MSK	MSK	CR	IR	IR	IR	ABS	ABS	MSK	MSK	CR
24	Process t	0.5	0.5	0.5	0.25	0.25	0.45	0.45	0.6	0.5	0.5	0.5	0.25	0.25	0.45	0.45	0.6
25	JOB 7	ADC	IR	IR	IR	ABS	ABS	MSK	MSK	ADC	IR	IR	IR	ABS	ABS	MSK	MSK
26	Process t	0.7	0.5	0.5	0.5	0.25	0.25	0.45	0.45	0.7	0.5	0.5	0.5	0.25	0.25	0.45	0.45
27	JOB 8		ADC	IR	IR	IR	ABS	ABS	MSK		ADC	IR	IR	IR	ABS	ABS	MSK
28	Process t		0.7	0.5	0.5	0.5	0.25	0.25	0.45		0.7	0.5	0.5	0.5	0.25	0.25	0.45
29	JOB 9		ADC	IR	IR	IR	ABS	ABS	MSK		ADC	IR	IR	IR	ABS	ABS	MSK
30	Process t		0.7	0.5	0.5	0.5	0.25	0.25	0.45		0.7	0.5	0.5	0.5	0.25	0.25	0.45
31	JOB 10		ADC	IR	IR	IR	ABS	ABS	MSK		ADC	IR	IR	IR	ABS	ABS	MSK
32	Process t		0.7	0.5	0.5	0.5	0.25	0.25	0.45		0.7	0.5	0.5	0.5	0.25	0.25	0.45
33	JOB 11				ADC	IR	IR	IR	ABS				ADC	IR	IR	IR	ABS
34	Process t				0.7	0.5	0.5	0.5	0.25				0.7	0.5	0.5	0.5	0.25
35	JOB 12					ADC	IR	IR	IR					ADC	IR	IR	IR
36	Process t					0.7	0.5	0.5	0.5					0.7	0.5	0.5	0.5
37	JOB 13								ADC					ADC	IR	IR	IR
38	Process t								0.7					0.7	0.5	0.5	0.5
39	JOB 14															ADC	IR
40	Process t															0.7	0.5
41	JOB 15																ADC
42	Process t																0.7
43	JOB 16																Ht
44	Process t																0.33
Sheet8

Cell Formulas
Range	Formula
B1,B2:Q2,J1	B1	=B11
I1,Q1	I1	=SUM(B9:I9)
B3:Q8	B3	=SUMPRODUCT(--(OFFSET(B$12,1,0,2*$A$11,1)=$A3),OFFSET(B$12,2,0,2*$A$11,1))
B9:Q9	B9	=SUM(B3:B8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Cell Value	="HT"	text	NO
1:1048576	Cell Value	="CR"	text	NO
1:1048576	Cell Value	="MSK"	text	NO
1:1048576	Cell Value	="ABS"	text	NO
1:1048576	Cell Value	="IR"	text	NO
1:1048576	Cell Value	="ADC"	text	NO

 

[JACOBB9900]

What i was trying to do is have a single table that updated on a daily basis but the first solution worked perfect. All i did was copy the formula across which gave me a result similar to the second example you posted. It works like i need it to.

As i was editing i came across a way to place all the data for an operation in one cell. I did this because i was adding a row for each piece of information i needed. For example Row A contained the operation, Row B contained the process time, Row C contained the machine process time, and Row D contained the machine number. This led to each job requiring four rows each. No one needs to see any of the data to the right of each job column. I have conditional formatting rules which color code each cell depending on certain criteria and all the text is at a font size 1. The text is also colored the same as the cell so you cant see it. Having so many rows with all this data was slowing down my worksheet which is why i combined all the data into one string. Also i am trying to make it as easy as possible for someone else to use when i'm not here. But what i need to figure out is how to pull the specific data from each cell depending on a criteria and SUM it all. I am having to track C/T (cycle time) hours and M/H (machine) hours separately. Each cell will contain the code i.e. ADC,IR,ABS,MCH,etc., the text "C/T"followed by the process time, the text "O/P"followed by the operation number, the text "M/H" followed by the machine hours. It will always be in that order as you can see in the cells in my example. I need the table titled C/T Hours to only SUM the numbers that come after "C/T" if that cell contains the code in B5:B11. The table titled M/H Hours needs to SUM the numbers that come after "M/H" if that cell contains the code in L5:L11.

Am i'm still pretty new to Excel and i really appreciate the help. I will continue to find a solution but any and all help is appreciated.

Thank you again KRice.

 

[KRice]

You have the constituent data to construct the text strings somewhere(?)...that would probably be the easiest and most efficient way to extract the information. Concatenation of text and numeric data somewhat complicates extraction of the relevant numeric data from the string. In the notional example below, you'll find two formulas that extract and sum the numeric data matching the process code in B5:B11. I've slightly modified that code (adding a * or #) to exactly match that used in the scheduling table (necessary for the formulas to work correctly). Others might have better ways to extract the numeric data, but this seems to work.

You mentioned something about updating the schedule on a daily basis. The summary tables above (for C/T and M/H) access the scheduling data found only in B15:I22. Do you have a strategy for specifying which day should be summarized in the two tables? For example, one would need to access data in J15:Q22 if the summary tables were to display information for Friday, March 6 2020.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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.10	0.00	0.00	0.00	0.00	0.00	0.00	0.00		1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6		IR	0.20	0.30	0.50	0.20	0.40	0.10	0.20	0.30		2.00	3.00	5.00	2.00	4.00	1.00	2.00	3.00
7		ABS	0.00	0.60	0.00	1.20	0.00	0.90	0.30	0.00		0.00	1.00	0.00	2.00	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.00	0.00	0.00	0.32	0.00		0.00	0.00	0.00	0.00	0.00	0.00	2.10	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
14	Jobs	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	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
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
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
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
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	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
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	IR 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
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
Sheet8 (2)

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

 

[JACOBB9900]

Hey KRice sorry for the late response I've been using the formulas you posted above and they are working wonders. It's the solution i needed. I do have all the information separated on another sheet but I don't know how i would extract and compile the data the way it is on the gantt chart without placing all the information in one cell like in the examples above. As for now what you gave me works great. I don't have a strategy for specifying which day should be summarized. I tried to put something together but the problem i was having was changing the cells the formula needs to extract the information from. In other words i couldn't figure out how to change the source of the data when the day changed. I think i'm going to use what you gave me for now and see if i can add to it but again thank you very much. Honestly it was a lot of help.