If you're willing to do a little prep to the input sheet, you can do it with either SUMIFS() or SUMPRODUCT(). The prep would involve this... put the product number on each row where there is an order (to the right of the quantity column). Secondly, if you use the SUMPRODUCT() solution, remove the pseudo underlines (---------). here's what I get:
This is awesome, works perfectly and it's GREAT karma you both are putting out in the universe being so helpful on here. Many, many, many, thanks for your time!
So it pains me throw one more wrinkle in the mix - the file from the baker comes in with the category titled cells merged (I.e. A2/B2, A7/B7, A12/B12), so when the formula has the "UNIT*| part, I have to unmerge those cells and type UNIT in the B2, B7 and B12 cells to get it to work right.
I tried all night to try and fix it myself but couldn't quite get it right.