Formula to sum multiple values from table if values in other set of cells matches criteria

Hello gurus,

I've attached the file I'm attempting to work with for reference.

I'm trying to create a formula in cell C14 that looks at the values in cells A8:A13, matches those values against corresponding data in the HC and CS per HR table, to sum the values from column D of those matching data points, kind of like a multiple vlookup/sum of lookup results, does that make sense?

Suggestions would be greatly appreciated!

Thanks in advance you beautiful minded geniuses :-)

George

Hi Ford, thanks for looking at this so quickly.

The expected answer for cell C14 would be 80, which is the sum of headcount values from column D in the HC and CS per HR sheet, for the corresponding SKUs listed in column A of the Scheduling template sheet, cells A8:A13

SKU Headcount Processing (column D)
11111 34.0
11112 8
11113 9
11114 9
11115 10
11116 10

George

BOOM! that did the trick, thank you good sir, your solution did the trick beautifully!

I appreciate you taking the time to help me, thank you, Ford!

Sincerely,

George

Hi Ford,

Sorry about this, turns out it's a little more complicated than I initially thought - there needs to be an additional layer of criteria in the formula that directs existing formula to only return headcount if there is case quantity data across from the material data being looked up.

for example, current formula is {=SUM(SUMIF('HC and Cases Per Hour'!$A$5:$A$132,A8:A13,'HC and Cases Per Hour'!$F$5:$F$132))} - it needs to also consider only returning a value for a match when cases are scheduled to be produced against it, and only return the headcount required for that material being produced.

So in the attached workbook, for the 6 examples listed, the expected result for the formula would be as follows - only returning headcount for matched mateirals that have corresponding production planned against them on the given day - does this make sense?

C14 = 34
D14 = 34
F14 = 10
G14 = 10
I14 = 11
J14 = 11
L14 = 13
M14 = 13
O14 = 14
P14 = 14
R14 = 14
S14 = 14

Sorry again Ford, I though we had it the first time around.

George

Hi Ford,

I'm feeling guilty here because I've been short sighted in my planning efforts and have failed to recognize scenarios that are appearing now as I work more with the new automation you've provided. In this case, the new scenario relates to one shift that has more than one material scheduled to run. The 2nd formula provided does exactly what I asked for it to do, except I'm realizing now that when multiple materials run back to back on the same shift, the formula returns an inflated headcount. So what I'm hoping can be done is that one final layer of criteria be added into the formula to only return the highest value of the two (or more) summed values when multiple materials are being run on the same shift.

For example, if material 11111, Example 1 is being run, and needs 5 headcount to make it, and it's followed by material 11112, Example 2, and it needs 10 headcount to make it, the formula currently returns 15, the sum of the two. Can an extra layer of criteria be added to the formula to tell it to only return the 10?

I've attached an updated example file, and have modified cells B8:B16 to show what the headcount is for each particular example, and have modified row 18 to show the expected value of the new formula in row 17.

I hope this makes sense. Thank you for the help Ford, this ought to be last time we revisit this, I appreciate your time!!

George

That's a very good point, and you're right. The bigger picture, we have three separate lines that are staffed by three different teams, so the formula for each line does not need to sum the headcount, only return the actual headcount required for the material being run if only one material is being run on the shift; or, if more than one material is being run on the same shift, the formula only needs to return the highest headcount of the materials being run on that shift. Does that make things easier?

As to the question whether more than 1 "11111 Example 1" will run on one shift, no, it might run on next shift or the day after, but not be documented as two separate line items on the schedule.

Ford, you are my hero, brother, thank you sir! This did the trick, for real this time - these array formulas are something else!

I really appreciate the time you took to resolve this, this solution is a huge help for our scheduling team!

George

LOL, just tried giving you rep again, but the site wants me to spread some reputation around before giving you more for your efforts.

Thanks Ford!

George