Returning a value in a column offset from its criteria

JACOBB9900

New Member
Joined
Apr 8, 2019
Messages
32
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.
GANTT HELP.PNG
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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
IJKLMNOPQR
1Tuesday
2Hrs12345678Process daily sum
3ADC0.72.100.70.7000.74.9
4IR112.522110.511
5ABS0.50.250.250.250.510.750.253.75
6MSK0.60.90.90.450.450.450.91.86.45
7CR0.61.21.22.42.4333.617.4
8HT000000000
9Hourly Sum3.45.454.855.86.055.455.656.8543.5
10Tuesday
11Hours12345678
12JOB 1CRCRCRCRCRCRCRCR
13Process t0.60.60.60.60.60.60.60.6
14JOB 2MSKCRCRCRCRCRCRCR
15Process t0.60.60.60.60.60.60.60.6
16JOB 3ABSMSKMSKCRCRCRCRCR
17Process t0.250.450.450.60.60.60.60.6
18JOB 4ABSMSKMSKCRCRCRCRCR
19Process t0.250.450.450.60.60.60.60.6
20JOB 5IRABSABSMSKMSKCRCRCR
21Process t0.50.250.250.450.450.60.60.6
22JOB 6IRIRIRABSABSMSKMSKCR
23Process t0.50.50.50.250.250.450.450.6
24JOB 7ADCIRIRIRABSABSMSKMSK
25Process t0.70.50.50.50.250.250.450.45
26JOB 8ADCIRIRIRABSABSMSK
27Process t0.70.50.50.50.250.250.45
28JOB 9ADCIRIRIRABSABSMSK
29Process t0.70.50.50.50.250.250.45
30JOB 10ADCIRIRIRABSABSMSK
31Process t0.70.50.50.50.250.250.45
32JOB 11ADCIRIRIRABS
33Process t0.70.50.50.50.25
34JOB 12ADCIRIRIR
35Process t0.70.50.50.5
36JOB 13ADC
37Process t0.7
Sheet8
Cell Formulas
RangeFormula
J1,J2:Q2J1=J10
J3:Q3J3=SUMPRODUCT(--(J$12:J$37="adc"),J$13:J$38)
R3:R8R3=SUM(J3:Q3)
J4:Q4J4=SUMPRODUCT(--(J$12:J$37="IR"),J$13:J$38)
J5:Q5J5=SUMPRODUCT(--(J$12:J$37="ABS"),J$13:J$38)
J6:Q6J6=SUMPRODUCT(--(J$12:J$37="MSK"),J$13:J$38)
J7:Q7J7=SUMPRODUCT(--(J$12:J$37="CR"),J$13:J$38)
J8:Q8J8=SUMPRODUCT(--(J$12:J$37="HT"),J$13:J$38)
J9:R9J9=SUM(J3:J8)
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2019
Messages
32

ADVERTISEMENT

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

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMNOPQ
1TuesdayDsum ->43.5WednesdayDsum ->47.23
2Hrs1234567812345678
3ADC0.72.100.70.7000.70.72.100.71.400.70.7
4IR112.522110.5112.5221.51.51.5
5ABS0.50.250.250.250.510.750.250.50.250.250.250.510.750.25
6MSK0.60.90.90.450.450.450.91.80.60.90.90.450.450.450.91.8
7CR0.61.21.22.42.4333.60.61.21.22.42.4333.6
8HT0000000000000000.33
9Hourly Sum3.45.454.855.86.055.455.656.853.45.454.855.86.755.956.858.18
10â MaxProc
1116TuesdayWednesday
12Hours1234567812345678
13JOB 1CRCRCRCRCRCRCRCRCRCRCRCRCRCRCRCR
14Process t0.60.60.60.60.60.60.60.60.60.60.60.60.60.60.60.6
15JOB 2MSKCRCRCRCRCRCRCRMSKCRCRCRCRCRCRCR
16Process t0.60.60.60.60.60.60.60.60.60.60.60.60.60.60.60.6
17JOB 3ABSMSKMSKCRCRCRCRCRABSMSKMSKCRCRCRCRCR
18Process t0.250.450.450.60.60.60.60.60.250.450.450.60.60.60.60.6
19JOB 4ABSMSKMSKCRCRCRCRCRABSMSKMSKCRCRCRCRCR
20Process t0.250.450.450.60.60.60.60.60.250.450.450.60.60.60.60.6
21JOB 5IRABSABSMSKMSKCRCRCRIRABSABSMSKMSKCRCRCR
22Process t0.50.250.250.450.450.60.60.60.50.250.250.450.450.60.60.6
23JOB 6IRIRIRABSABSMSKMSKCRIRIRIRABSABSMSKMSKCR
24Process t0.50.50.50.250.250.450.450.60.50.50.50.250.250.450.450.6
25JOB 7ADCIRIRIRABSABSMSKMSKADCIRIRIRABSABSMSKMSK
26Process t0.70.50.50.50.250.250.450.450.70.50.50.50.250.250.450.45
27JOB 8ADCIRIRIRABSABSMSKADCIRIRIRABSABSMSK
28Process t0.70.50.50.50.250.250.450.70.50.50.50.250.250.45
29JOB 9ADCIRIRIRABSABSMSKADCIRIRIRABSABSMSK
30Process t0.70.50.50.50.250.250.450.70.50.50.50.250.250.45
31JOB 10ADCIRIRIRABSABSMSKADCIRIRIRABSABSMSK
32Process t0.70.50.50.50.250.250.450.70.50.50.50.250.250.45
33JOB 11ADCIRIRIRABSADCIRIRIRABS
34Process t0.70.50.50.50.250.70.50.50.50.25
35JOB 12ADCIRIRIRADCIRIRIR
36Process t0.70.50.50.50.70.50.50.5
37JOB 13ADCADCIRIRIR
38Process t0.70.70.50.50.5
39JOB 14ADCIR
40Process t0.70.5
41JOB 15ADC
42Process t0.7
43JOB 16Ht
44Process t0.33
Sheet8
Cell Formulas
RangeFormula
B1,B2:Q2,J1B1=B11
I1,Q1I1=SUM(B9:I9)
B3:Q8B3=SUMPRODUCT(--(OFFSET(B$12,1,0,2*$A$11,1)=$A3),OFFSET(B$12,2,0,2*$A$11,1))
B9:Q9B9=SUM(B3:B8)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
1:1048576Cell Value="HT"textNO
1:1048576Cell Value="CR"textNO
1:1048576Cell Value="MSK"textNO
1:1048576Cell Value="ABS"textNO
1:1048576Cell Value="IR"textNO
1:1048576Cell Value="ADC"textNO
 

JACOBB9900

New Member
Joined
Apr 8, 2019
Messages
32
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.

GANTT HELP 2.PNG
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRS
3C/T HOURSM/H HOURS
4HRS1234567812345678
5ADC0.100.000.000.000.000.000.000.001.000.000.000.000.000.000.000.00
6IR0.200.300.500.200.400.100.200.302.003.005.002.004.001.002.003.00
7ABS0.000.600.001.200.000.900.300.000.001.000.002.000.001.500.500.00
8MCH#0.210.210.070.140.070.280.210.281.501.500.501.000.502.001.502.00
9CR0.000.000.000.000.000.000.320.000.000.000.000.000.000.002.100.00
10WLD*0.400.000.400.000.400.000.200.201.000.001.000.001.000.000.500.50
11OUT0.000.000.000.000.100.000.000.000.000.000.000.001.000.000.000.00
12
13Thursday, March 5 2020Friday, March 6 2020
14Jobs1234567812345678
15JOB 1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1
16JOB 2MCH# C/T0.07 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5CR C/T0.32 O/P20 M/H2.1MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5
17JOB 3WLD* C/T0.2 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5ABS# C/T0.3 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5ABS# C/T0.3 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5
18JOB 4MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5
19JOB 5ADC C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ADC C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1
20JOB 6IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5OUT C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5OUT C/T0.1 O/P20 M/H1MCH# C/T0.07 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1
21JOB 7MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5ABS# C/T0.3 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5ABS# C/T0.3 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5
22JOB 8WLD* C/T0.2 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5IR C/T0.1 O/P20 M/H1IR C/T0.1 O/P20 M/H1ABS# C/T0.3 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5MCH# C/T0.07 O/P30 M/H0.5WLD* C/T0.2 O/P30 M/H0.5
Sheet8 (2)
Cell Formulas
RangeFormula
C5:J11C5=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:S11L5=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

New Member
Joined
Apr 8, 2019
Messages
32
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,710
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top