Help with SUMPRODUCT formula

[Woodpusher147]

Hello
I have a sheet which calculates scores depending on cell colour.
Cell E7 contains a formula which calculates points and gives them a multiple of the colour scores
Gold = 5 so if column G is worth 20 for gold then the formula multiplies by 4

Column D has a dropdown with Bedroom - Upstairs or Downstairs

At the moment, the formula calculates points for both upstairs and downstairs and then a different calculation if COlumn D = Bedroom

I need to add another array so I can calculate differently for Upstairs therefore giving me different scores for all departments

I hope thats clear but here is the formula which may make it clear

=SUMPRODUCT(CFV(F11:Q11),IF(D11="Bedroom",{4,2,4,0,0,0,0,0,5,2,2,1},{4,2,4,4,1,1,2,1,0,0,0,1}))

I just to make another IF so if column D= Upstairs it would calculate 4,2,4,4,1,1,2,1,0,0,0,5

HOpe this is clear and someone can help


Thanks

 

[awoohaw]

Would you be kind enough and use the xl2bb add in to post a sample of your worksheet (link below). If you cannot use the add in, then please post a table.
Expected results would help.

Thanks in advance.

 

[Special-K99]

Isn't this just a nested IF statement?

=SUMPRODUCT(CFV(F11:Q11),IF(D11="Bedroom",{4,2,4,0,0,0,0,0,5,2,2,1},IF(D11="Downstairs",{4,2,4,4,1,1,2,1,0,0,0,1},{4,2,4,4,1,1,2,1,0,0,0,5})))

Checks for Bedroom, else checks for Downstairs, otherwise assumes D11 is the Upstairs array (though it could be any value of course)

 

[Woodpusher147]

Isn't this just a nested IF statement?

=SUMPRODUCT(CFV(F11:Q11),IF(D11="Bedroom",{4,2,4,0,0,0,0,0,5,2,2,1},IF(D11="Downstairs",{4,2,4,4,1,1,2,1,0,0,0,1},{4,2,4,4,1,1,2,1,0,0,0,5})))

Checks for Bedroom, else checks for Downstairs, otherwise assumes D11 is the Upstairs array (though it could be any value of course)

Thats excellent, thank you very much