Microsoft Excel wizards to the white courtesy phone please

Surely there are some Excel gurus around here. I’ve run into a problem I need some help with. See attached photos. I want excel to lookup values based on criteria then sum those values all in one cell.

For example, Jobs 1 and 3 apply to SKU Two as indicated by the placement of “1” in those boxes. I need excel to then refer to sheet two, find the corresponding cycle values for jobs 1 and 3 only and sum them in the “C/T” box on sheet 1.

I’ve tried every combination I can think of with sum, if, sumifs, index/match, lookup functions, etc and just cannot for the life of me get it to return the desired value. Maybe I just have a case of the Mondays but any help would be greatly appreciated.

 

Real men use SQL.

Name your sheets brah. I renamed sheet2 to data_reference and sheet1 to sku_calc Excel is pretty limited, the easiest way to deal with use cases like these is to return a zero for a false condition and sum it.

I would redesign this, it's pretty inflexible as is and you will struggle with more SKUs and if you want to test more conditions.

IF($B2=1, INDEX(data_reference!$E$2:$E$4, MATCH(sku_calc!B$1, data_reference!$A$2:$A$4, 0)), 0)
+IF($C2=1, INDEX(data_reference!$E$2:$E$4, MATCH(sku_calc!C$1, data_reference!$A$2:$A$4, 0)), 0)
+IF($D2=1, INDEX(data_reference!$E$2:$E$4, MATCH(sku_calc!D$1, data_reference!$A$2:$A$4, 0)), 0)

 

Here's a quick and dirty write up using 1s and 0s (any other inputs will require additional logic in the formula). Let me know if this works

 

I thought this was going to be a challenging query...like how to make bold borders or merge-n-center cells.

 

I’m guessing there is no quick formula for this. I should’ve mentioned that my actual application of this will apply to hundreds of SKUs and jobs so just summing individual formulas for each isn’t practical. Hence my question. Thank you for the responses though.

 

It’s not simple because the data schema isn’t well designed for calculating a large number of values or conditions. It’d be easier if you stored all data in rows instead of 2 dimensionally. Then you could just pivot it or use simple sumifs for the final calculation.

 

This.

 

Well looks like you guys have this handled.

I'm currently writing reformat macros for an entire database migration. They want to use Excel as an intermediate step instead of spend $$$$ on some outside firm to do it on the back-end. Somebody kill me.