Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    SUMIF FORMULA HELP

    ✭✭✭✭
    in Formulas and Functions

    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?

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • Community Champion
      edited 12/18/23 Answer ✓

      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.

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓
      Stefan

      Does this work?

      =SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi @Stefan,


      Thanks for the quick reply. Just tried you suggestion. I get the #unparseable as a response.


      Any thoughts?

      Many thanks

      Ed

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/18/23 Answer ✓

      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.

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓
      Stefan

      Does this work?

      =SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Brilliant, thanks all for the help

      · Share on FacebookShare on Twitter
    • Community Champion

      Anytime 👍

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter
    • Community Champion

      @Ed Gadd,

      happy to help :-)

      Stefan

      Smartsheet Consulting, Solution Building, Training and Support.

      Projects for Processes and for People.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/18/23
      Ed Gadd

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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?

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/18/23
      Ed Gadd

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      @SteyJ

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

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/18/23

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      @SteyJ ,


      Amazing, thankyou so much. Works brilliantly. Great education this afternoon too. Many many thanks


      Ed

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions