# SUMIFS

Options
✭✭✭✭

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:

• Overachievers Alumni
Options

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

• Overachievers Alumni
Options

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

• ✭✭✭✭
Options

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

• Overachievers Alumni
Options

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

• ✭✭✭✭
edited 06/20/24
Options

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

• Overachievers Alumni
Options

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

• ✭✭✭✭
Options

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?

• Overachievers Alumni
Options

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

• ✭✭✭✭