SUMIFS

rachmavis

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?

Brian_Richardson

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

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

rachmavis

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

Brian_Richardson

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])

rachmavis

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

Brian_Richardson

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

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

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})

rachmavis

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