Excel Trying to calculate data from multiple cells with IF variables

[jverango]

I keep running into errors trying to solve this formula. I am needing to have E8 total up several cells with possible IF variables (not sure it IF is even the correct choice). Plus this type of calculation is way above my level of knowledge in Excel. I am pulling data for this formula from two worksheets in the same workbook. The worksheet where it is calculating is "Measure" (left image) and the other worksheet where it is pulling additional variables from is "Factors" (right image). I am currently working on this with Google Sheets but could transfer to either Excel 2007 or 2010 if needed. I am not even sure if it's possible but if it is I am greatly appreciative to learn how to solve it. Also if it is easier for it to calculate from one worksheet I am fine with adding in the additional info from "Factor" to the same worksheet.

Example of what I need calculated
Elite = UI x Style (C3+D4 =IF "PW", C4+D4= "XO", C5+D5 "XOX"), Grid IF "Yes", Obs IF "Yes", Temp IF "Yes", Up Color IF "yes

1. The final calculation needs to be in E8
2. In the Measure worksheet I am trying to multiply J8 x S8 (add in IF's to determine correct multiplier from the Factor worksheet based on PW = C3+D3, XO = C4+D4, & XOX =C5+D5)
3. Then it needs to add in from U8 IF yes, W8 IF yes, Y8 IF yes with the values coming from the Factor worksheet cells C6:C8
4. It also needs to add in AA8 IF yes from the Factor worksheet cell B9
5. But also it needs to factor 0 if J8 is a 0 value so E8 would be 0 value when it gets added to my SUM formula for all of column E on the Measure worksheet
6. The current figures in E8 & H8 are the correct sums (just manually added) for reference.

 

[teylyn]

First thing to note: don't merge all these cells to create wide columns. This makes addressing the correct cells in formulas harder than it needs to be. Instead of merging three narrow columns, just make the column with the actual data as wide as you need it. If you need, center labels across multiple columns. Merged cells lead to all kinds of problems.

 

[jverango]

The only reason I had to do that is for the bottom portion of the worksheet (not shown), which includes order details and checkboxes.

 

[teylyn]

this bit: Style (C3+D4 =IF "PW", C4+D4= "XO", C5+D5 "XOX")

You can use Vlookups to get the values based on the entry in the Style column

VLookup(S8,Factor!$B$3:$D$5,2,false)+VLookup(S8,Factor!$B$3:$D$5,3,false)


This bit: Grid IF "Yes", Obs IF "Yes", Temp IF "Yes", Up Color IF "yes

You can use IF statements.

if(U8="yes",Factor!$C$8,0)

To get you started:

=(J8*VLookup(S8,Factor!$B$3:$D$5,2,false)+VLookup(S8,Factor!$B$3:$D$5,3,false))+if(U8="yes",Factor!$C$8,0)+if(W8="yes",Factor!$C$6,0)+if(Y8="yes",Factor!$C$7,0)+if(AA8="yes",Factor!$B$9,0)

Don't prefill formulas into empty rows, then you don't need to factor in zero values. Use an Excel table, which will copy formulas and formatting to new rows automatically.

 

[jverango]

this bit: Style (C3+D4 =IF "PW", C4+D4= "XO", C5+D5 "XOX")

You can use Vlookups to get the values based on the entry in the Style column

VLookup(S8,Factor!$B$3:$D$5,2,false)+VLookup(S8,Factor!$B$3:$D$5,3,false)


This bit: Grid IF "Yes", Obs IF "Yes", Temp IF "Yes", Up Color IF "yes

You can use IF statements.

if(U8="yes",Factor!$C$8,0)

To get you started:

=(J8*VLookup(S8,Factor!$B$3:$D$5,2,false)+VLookup(S8,Factor!$B$3:$D$5,3,false))+if(U8="yes",Factor!$C$8,0)+if(W8="yes",Factor!$C$6,0)+if(Y8="yes",Factor!$C$7,0)+if(AA8="yes",Factor!$B$9,0)

Don't prefill formulas into empty rows, then you don't need to factor in zero values. Use an Excel table, which will copy formulas and formatting to new rows automatically.

Thank you so much, that worked except for my error I need to make AA8 multiply against Factor!$B$9 IF yes, I keep trying different variations but everything errors on me. Any suggestions?