Math formulas across multiple cells

Kathryn Pineda

Hello all,

Any help on the following issue would be very much appreciated. I have a scoring matrix built in to Smartsheet (See below). I would like to multiply the score of each cell for each criteria (Strategic Init Impact Score, Business Impact, etc.) by the corresponding weight (and divide each by 100) and then add those 7 scores together to find the final score. For reference the scoring criteria follows.

3/Yes - 100%

2 - 66%,

1 - 33%;

0/No - 0%

First row manually calculated as: (100*15/100) + (100*25/100) + (100*15/10) + (100*10/100) + (100*10/100) + (0*10/100) + (100*15/100) = Total Score of 90 (the 100 is a typo)

A million thanks in advance!! (I hope this is possible as I have about 250 items to score :))

** Note - the Total Scores already calculated were done by hand **

Bassam Khalil

Hi @Kathryn Pineda 

Hope you are fine, Please try the following formula and convert it to column format formula:

=IFERROR(IF(OR([Criteria 1]@row = 3, [Criteria 1]@row = "Yes"), 15, IF([Criteria 1]@row = 2, 
15 * 0.66, IF(OR([Criteria 1]@row = 1, [Criteria 1]@row = "No"), 0.33 * 15))) + 
IF(OR([Criteria 2]@row = 3, [Criteria 2]@row = "Yes"), 25, IF([Criteria 2]@row = 2, 25 * 0.66, 
IF(OR([Criteria 2]@row = 1, [Criteria 2]@row = "No"), 0.33 * 25))) + 
IF(OR([Criteria 3]@row = 3, [Criteria 3]@row = "Yes"), 15, IF([Criteria 3]@row = 2, 
15 * 0.66, IF(OR([Criteria 3]@row = 1, [Criteria 3]@row = "No"), 0.33 * 15))) + 
IF(OR([Criteria 4]@row = 3, [Criteria 4]@row = "Yes"), 10, IF([Criteria 4]@row = 2, 10 * 0.66, 
IF(OR([Criteria 4]@row = 1, [Criteria 4]@row = "No"), 0.33 * 10))) + IF(OR([Criteria 5]@row = 
3,[Criteria 5]@row = "Yes"), 10, IF([Criteria 5]@row = 2, 10 * 0.66, IF(OR([Criteria 5]@row = 1
,[Criteria 5]@row = "No"), 0.33 * 10))) + IF(OR([Criteria 6]@row = 3, [Criteria 6]@row = "Yes"),
10, IF([Criteria 6]@row = 2, 10 * 0.66, IF(OR([Criteria 6]@row = 1, [Criteria 6]@row = "No"),
0.33 * 10))) + IF(OR([Criteria 7]@row = 3, [Criteria 7]@row = "Yes"), 15, 
IF([Criteria 7]@row = 2, 15 * 0.66, IF(OR([Criteria 7]@row = 1, [Criteria 7]@row = "No"),
0.33 * 15))), "")

the Following screenshot shows the result:

Please be sure to use the same columns names in my formula and all columns are test/number type.