Order of Operations -- PEMDAS syntax

Hello, I am currently designing a Quiz, but I am having an issue with my syntax and I was hoping someone could explain what I'm doing wrong. Basically, I have a test that is worth 208 total points.

Let's say that there are 84 questions and each question is worth 1, 2, 4, or 6 points.

Here is where the twist comes in. Sometimes the answer to a question will be "N/A". So for example, if there is one N/A answer on a question that is worth 2 points, I need the the total to shift from 208 to 206 total points.

What I currently have done is have N/A be worth the inverse of a full credit. So if a question was worth 2 points if they are correct, I would have the N/A value be -2. Then I have 2 helper columns to run a SUMIF. Helper column 1 sums up all the number values above 0, and then Helper column 2 sums up all the number less than 0 and then multiplies it by -1 to get a positive value.

Then my result column basically: Helper Column 1 / (208 - Helper Column 2). This works, but I was wondering if there was a way to rewrite it so I didn't have to use 2 additional helper columns

=(SUMIF([Data Range], > 0) / (208 - ((SUMIF([Data Range] < 0) *-1))

However, I keep getting a syntax error. Am I missing something or is this a smartsheet limitation?


  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Dan B.

    Is this the formula copied directly from your cell:

    =(SUMIF([Data Range], > 0) / (208 - ((SUMIF([Data Range] < 0) *-1))

    If so, I think your syntax error is caused by this part: [Data Range]

    If Data Range is the column name, to SUM the entire column, you would use [Data Range]:[Data Range]

    If it is a cross sheet reference to a column in another sheet it would be {Data Range}

  • Dan B.
    Dan B. ✭✭✭✭
    edited 03/21/24

    Sorry for the delay on it, but it's [Data Range]@row:[Data Range]@row

    The way my sheet is set up is that each column is an "answer" and the column next to it is a "point value". The answers are text, the point values are number values.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!