Add column values where another column meets a criteria

Options

Hello,

I'm trying to add up the values in "Hours per week" where the "Employee name" does not equal "OPEN". Where am I going wrong?

Any help appreciated!

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/03/24
    Options

    @Remy V

    As everything is on the same sheet. You don't need to do an index collect.

    =Sumifs([Hours Per Week]1:[Hours Per Week]10,[Employee Name]1:[Employee Name]10<>"Open")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    I noticed a slight syntax error in the formula I gave. This new formula fixes it.

    =Sumifs([Hours Per Week]1:[Hours Per Week]10,[Employee Name]1:[Employee Name]10,<>"Open")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Remy V
    Remy V ✭✭
    Options

    @Mark.poole is there a way to do this without noting the end row? I want it to sum all the above columns that match the criteria even if extra rows are added.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/05/24
    Options

    @Remy V

    The problem is if you don't notate the end row it will try to count itself which is what's causing the error.

    IF you do a hierarchy. Making the rows you want to sum as the children. Move the master total to the top of the names. Indent all the names to be children of Master total. Then you can do

    =SUMIFS(CHILDREN([Hours per week]1), CHILDREN([Employee Name]1), "Open")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!