SUMIF and HLOOKUP or another solution?

[might_be_high]

I am just getting back into using excel, so I don't remember if this has an easy solution.

(Background) I am working with a company that uses SAP for their order entry. Reps fill out an order form similar to the linked example test environment. We are working with over 500 possible SKU's depending on a few item descriptions, ie. Base material, Design, and Size.

(Question) Is there a way I can write a formula to find the sum of each individual "product" (Item Code and Size make the product unique) and display it in a column for a CS upload sheet. Would some kind of Sumif/Lookup formula work? Perhaps a combination of index and match?

You should know that sometimes there are skips in sizes, currently, I have the missing size cells locked so reps can tab through when filling out the order form. IDK if this makes a difference. I have attached an example test enviroment.


https://drive.google.com/open?id=0B7YALexAFUYWd0R5MTQxTUlhZ2c

BTW. Let me know if I messed up any formatting here. First post.

[63falcondude]

Hello and welcome to the forum.

In E2 of the 'CS Upload' worksheet, try this

=SUMPRODUCT(('Order Form'!B$2:B$5=A2)*('Order Form'!C$2:C$5=B2)*('Order Form'!D$1:L$1=C2)*('Order Form'!D$2:L$5))

You didn't include the expected results for us to compare the formula results to so I'll leave the checking to you.

[cbatrody]

Or,

Try the below array formula in E2 (CS Upload tab)

Please Login or Register  to view this content.

to be confirmed by pressing CTRL+SHIFT+ENTER

See the attached file.

[Richard Buttrey]

I think there's also a requirement to generate the CS Upload unique items from the Order Form rather than populating something that already exists.
Maybe the OP will confirm?

[might_be_high]

This works perfectly!

Thank you so much!

[63falcondude]

Not sure which post you are referring to but I'm glad that we could help.

[might_be_high]

No need to generate unique CS Upload items. Those have already been generated but not included in the Order Form.

cbatrody's response worked.