SUMIFS

I can not get this simple formula to work:

=SUMIFS([Grade K: RGR Countdown Student Workbook]:[RGR Countdown Student Workbook]):(If[School/Location: "Amity Creek"])

When I click on the Columns, I get errors - how do I state everything in the column?

Tags:

Best Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    Couple of things:

    • You are using a : instead of a , to separate the arguments in your SUMIFS. Need a comma.
    • Square brackets [] go around the column name only, not around the logic. They are just a way to tell Smartsheet "ignore the spaces in this column name"
    • Your SUMIFS is already doing "if it equals" type of evaluation, so you don't bury another If in the middle of it.
    • Since you have only 1 criteria you can use SUMIF instead of SUMIFS. SUMIF arguments are SUMIF( range, criteria, sum range)

    =SUMIF([School/Location]:[School/Location], "Amity Creek",[Grade K: RGR Countdown Student Workbook]:[Grade K: RGR Countdown Student Workbook])

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    It actually is ok to have a / or : in your column names. As long as you wrap that column name with [] in your formula then all is good.

    Ex.

    [ My Column: Best] is good. ie =COUNT( [My Column: Best]:[My Column: Best])

    [ My Column / Best] is good. ie = COUNT( [My Column / Best]:[My Column / Best])

    [ My Column] /Best doesn't work. ie = COUNT( [My Column]/Best:[My Column]/Best) ← #UNPARSEABLE

    My Column: Best doesn't work. ie =COUNT( My Column:Best:My Column:Best) ← #UNPARSEABLE

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • rachmavis
    rachmavis ✭✭✭✭
    Answer ✓

    AMAZING! Thank you!!! I owe you a coffee!

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    Couple of things:

    • You are using a : instead of a , to separate the arguments in your SUMIFS. Need a comma.
    • Square brackets [] go around the column name only, not around the logic. They are just a way to tell Smartsheet "ignore the spaces in this column name"
    • Your SUMIFS is already doing "if it equals" type of evaluation, so you don't bury another If in the middle of it.
    • Since you have only 1 criteria you can use SUMIF instead of SUMIFS. SUMIF arguments are SUMIF( range, criteria, sum range)

    =SUMIF([School/Location]:[School/Location], "Amity Creek",[Grade K: RGR Countdown Student Workbook]:[Grade K: RGR Countdown Student Workbook])

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • rachmavis
    rachmavis ✭✭✭✭
    edited 06/20/24

    Thank you! Also, a coworker advised me to remove all : and / that were apart of my column headers…

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    It actually is ok to have a / or : in your column names. As long as you wrap that column name with [] in your formula then all is good.

    Ex.

    [ My Column: Best] is good. ie =COUNT( [My Column: Best]:[My Column: Best])

    [ My Column / Best] is good. ie = COUNT( [My Column / Best]:[My Column / Best])

    [ My Column] /Best doesn't work. ie = COUNT( [My Column]/Best:[My Column]/Best) ← #UNPARSEABLE

    My Column: Best doesn't work. ie =COUNT( My Column:Best:My Column:Best) ← #UNPARSEABLE

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • rachmavis
    rachmavis ✭✭✭✭

    Hi - Now I am trying to reference the same information into another sheet:

    =SUMIFS({School}:{School}, "Amity Creek",{Grade K RGR Countdown Student Workbook}:{Grade K RGR Countdown Student Workbook})

    I am getting an UNPARC error…I know it has to do with the brackets?

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    when you reference another sheet then the cross sheet reference means the whole column, so you don’t have to put it twice like in-sheet references.

    Also, if you look at the SUMIFS pop up you’ll see that it’s actually range, criteria range, criteria… so the arguments you provided don’t work in that order. Since you only have 1 criteria you can use SUMIF instead

    =SUMIF({School}, "Amity Creek",{Grade K RGR Countdown Student Workbook})

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • rachmavis
    rachmavis ✭✭✭✭
    Answer ✓

    AMAZING! Thank you!!! I owe you a coffee!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!