# Math formulas across multiple cells

Options
✭✭✭✭
edited 08/01/21

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 **

Tags:

• ✭✭✭✭✭✭
Options

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.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

As i notify you in my post use the same columns name, i fix it as per your columns name this is the rectified formula:

```=IF(ISBLANK([CHANGE MANAGEMENT REQS]@row), "",
IFERROR(IF(OR([Strategic Initiative Impact Score]@row = 3,
[Strategic Initiative Impact Score]@row = "Yes"), 15,
IF([Strategic Initiative Impact Score]@row = 2, 15 * 0.66,
IF(OR([Strategic Initiative Impact Score]@row = 1, [Strategic Initiative Impact Score]@row
="No"), 0.33 * 15))) + IF(OR([BUSINESS VALUE]@row = 3, [BUSINESS VALUE]@row = "Yes"), 25,
[BUSINESS VALUE]@row = "No"), 0.33 * 25))) + IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 3,
[NUMBER OF EMPLOYEES AFFECTED]@row = "Yes"), 15, IF([NUMBER OF EMPLOYEES AFFECTED]@row = 2,
15 * 0.66, IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 1, [NUMBER OF EMPLOYEES AFFECTED]@row
= "No"), 0.33 * 15))) + IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 3,
[REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "Yes"), 10,
IF([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 2, 10 * 0.66,
IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 1,
[REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "No"), 0.33 * 10))) +
IF(OR([REGULATORY COMPLIANCE]@row = 3, [REGULATORY COMPLIANCE]@row = "Yes"), 10,
IF([REGULATORY COMPLIANCE]@row = 2, 10 * 0.66, IF(OR([REGULATORY COMPLIANCE]@row = 1,
[REGULATORY COMPLIANCE]@row = "No"), 0.33 * 10))) + IF(OR([RESPONSE TO AUDIT?]@row = 3,
[RESPONSE TO AUDIT?]@row = "Yes"), 10, IF([RESPONSE TO AUDIT?]@row = 2, 10 * 0.66,
IF(OR([RESPONSE TO AUDIT?]@row = 1, [RESPONSE TO AUDIT?]@row = "No"), 0.33 * 10))) +
IF(OR([CHANGE MANAGEMENT REQS]@row = 3, [CHANGE MANAGEMENT REQS]@row = "Yes"), 15,
IF([CHANGE MANAGEMENT REQS]@row = 2, 15 * 0.66, IF(OR([CHANGE MANAGEMENT REQS]@row = 1,
[CHANGE MANAGEMENT REQS]@row = "No"), 0.33 * 15))), ""))
```

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

@Bassam Khalil A TREMENDOUS THANKS!!

«1

• ✭✭✭✭✭✭
Options

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.

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

Hi Bassam,

Thank you for your help! I copied the formula directly and I am getting an #UNPARESEABLE error...

• ✭✭✭✭
Options

Perhaps I just need to adjust my row numbers which I am trying right now. A huge thank you!

• ✭✭✭✭✭✭
Options

Please share me as an admin on a copy of your sheet and i will check what is the problem because it's work on my sample the following is a link to my sample sheet you can check it:

My Email to share: Bassam.k@mobilproject.it

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

Will do!

• ✭✭✭✭✭✭
Options

No need for row numbers, it's column format formula will work for any number of rows

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

@Bassam Khalil I have shared sheet, would you mind taking a look please? Many thanks!

• ✭✭✭✭
Options

@Bassam Khalil You are now an admin.

• ✭✭✭✭✭✭
Options

As i notify you in my post use the same columns name, i fix it as per your columns name this is the rectified formula:

```=IF(ISBLANK([CHANGE MANAGEMENT REQS]@row), "",
IFERROR(IF(OR([Strategic Initiative Impact Score]@row = 3,
[Strategic Initiative Impact Score]@row = "Yes"), 15,
IF([Strategic Initiative Impact Score]@row = 2, 15 * 0.66,
IF(OR([Strategic Initiative Impact Score]@row = 1, [Strategic Initiative Impact Score]@row
="No"), 0.33 * 15))) + IF(OR([BUSINESS VALUE]@row = 3, [BUSINESS VALUE]@row = "Yes"), 25,
[BUSINESS VALUE]@row = "No"), 0.33 * 25))) + IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 3,
[NUMBER OF EMPLOYEES AFFECTED]@row = "Yes"), 15, IF([NUMBER OF EMPLOYEES AFFECTED]@row = 2,
15 * 0.66, IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 1, [NUMBER OF EMPLOYEES AFFECTED]@row
= "No"), 0.33 * 15))) + IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 3,
[REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "Yes"), 10,
IF([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 2, 10 * 0.66,
IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 1,
[REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "No"), 0.33 * 10))) +
IF(OR([REGULATORY COMPLIANCE]@row = 3, [REGULATORY COMPLIANCE]@row = "Yes"), 10,
IF([REGULATORY COMPLIANCE]@row = 2, 10 * 0.66, IF(OR([REGULATORY COMPLIANCE]@row = 1,
[REGULATORY COMPLIANCE]@row = "No"), 0.33 * 10))) + IF(OR([RESPONSE TO AUDIT?]@row = 3,
[RESPONSE TO AUDIT?]@row = "Yes"), 10, IF([RESPONSE TO AUDIT?]@row = 2, 10 * 0.66,
IF(OR([RESPONSE TO AUDIT?]@row = 1, [RESPONSE TO AUDIT?]@row = "No"), 0.33 * 10))) +
IF(OR([CHANGE MANAGEMENT REQS]@row = 3, [CHANGE MANAGEMENT REQS]@row = "Yes"), 15,
IF([CHANGE MANAGEMENT REQS]@row = 2, 15 * 0.66, IF(OR([CHANGE MANAGEMENT REQS]@row = 1,
[CHANGE MANAGEMENT REQS]@row = "No"), 0.33 * 15))), ""))
```

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

@Bassam Khalil A TREMENDOUS THANKS!!

• ✭✭✭✭
Options

Hi Bassam, I am hoping you can help. For some reason the calculations are off. I went in and adjusted a few of the weights in the column formula but still calculations made no sense. I will add you ad an admin and hope you could take a quick look. For example in the first row I have all 0's for each field yet it is still netting a score. in the second row if I select a 2 for first criteria the total score should be 25 and that's not correct. Any help is greatly appreciated!!

• ✭✭✭✭
Options

And just to clarify, the scores in each field (3, 2, 1, Yes or No) correspond to the values below:

CRITERIA SCORE SCORE VALUE

1= 33%

2 = 66%

0/No = 0%

3/Yes = 100%

• ✭✭✭✭
Options

I tried to reference the formula above in your sample but the numbers are slightly off there as well. Thank you again.

• ✭✭✭✭✭✭
Options

bassam.khalil2009@gmail.com