SUMIF FORMULA HELP
I have the formula
=SUM({Tracker COST BENEFIT})
This totals the whole column of another sheet, I'd like to filter that total by counting only the items which are closed.
I've tried
=SUMIFS({Tracker COST BENEFIT}, ({Tracker STATUS}, "closed"))
But this doesn't seem to work Anyone have any ideas?
Best Answers
-
Hi @Ed Gadd,
ups, sorry, obviously I should try to read before writing ;-)
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")
Too many brackets.
Hope now this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Does this work?
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")
Sincerely,
Jacob Stey
Answers
-
Hi @Ed Gadd,
I think the logical condition is not there ;-)
=SUMIFS({Tracker COST BENEFIT}, ({Tracker STATUS}, ="closed"))
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Hi @Stefan,
Thanks for the quick reply. Just tried you suggestion. I get the #unparseable as a response.
Any thoughts?
Many thanks
Ed
-
Hi @Ed Gadd,
ups, sorry, obviously I should try to read before writing ;-)
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")
Too many brackets.
Hope now this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Does this work?
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")
Sincerely,
Jacob Stey
-
Brilliant, thanks all for the help
-
-
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Just in interest of learning. If I now wished to calculate the cost benefit of closed items per month would this be the correct approach?
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
I got the result incorrect argument.
-
I don’t think you need the and functions, instead just separate them with commas. The reason why you are getting incorrect argument is because you aren’t giving the ifs statement a date column to check. The formula for checking date appears to be correct though
im assuming you would like to check the data for January of 2023?
Sincerely,
Jacob Stey
-
Hi @SteyJ ,
Thanks for your help
Yes for this example I'd like to check January 2023
I've removed the AND
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", (IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
Still not quite working #unparseable. I feel its just a comma or bracket in the wrong place?
-
After “closed”, add the {date column} use the same sheet reference for checking the year.
sumifs takes a range then criteria, so you just need to add the criteria for date
it would look like this
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", {Date}, IFERROR(MONTH(@cell), 0) = 1, {Date}, IFERROR(YEAR(@cell), 0) = 2023)
Sincerely,
Jacob Stey
-
Thanks for all the support today. I reference the date column but still no success. Any thoughts?
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", {Tracker ECD}, (IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
-
Make sure you are enclosing your IFERROR properly. Try this:
=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", {Tracker ECD}, IFERROR(MONTH(@cell), 0) = 1, {Tracker ECD}, IFERROR(YEAR(@cell), 0) = 2023)
Sincerely,
Jacob Stey
-
@SteyJ ,
Amazing, thankyou so much. Works brilliantly. Great education this afternoon too. Many many thanks
Ed
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!