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?
Best Answers
-
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])
-
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
-
AMAZING! Thank you!!! I owe you a coffee!
Answers
-
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])
-
Thank you! Also, a coworker advised me to remove all : and / that were apart of my column headers…
-
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
-
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?
-
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})
-
AMAZING! Thank you!!! I owe you a coffee!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!