I need help summing up the value of one column based on the status of another

I have two sheets I am using— one with all my data, reference points, and another with all my formulas.

I want to sum up all the numbers in my 'Annual Revenue' column based on the Status column (completed, in progress, etc) of that same row.

I want to use this formula in my other sheet which holds all formulas and data. i am using the below formula but keep getting UNPARSABLE:

=SUMIF({Total Revenue}:{Total Revenue}, {StatusRange 1}:{StatusRange 1}, "Completed")

Please help! I've given myself a headache trying to figure this out.

Thank you in advance!

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓

    you were almost there. When you create a column reference as a cross sheet reference you don’t need the : functionality. Instead this should work.

    =SUMIFs({Total Revenue}, {StatusRange 1}, "Completed")

    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.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓

    you were almost there. When you create a column reference as a cross sheet reference you don’t need the : functionality. Instead this should work.

    =SUMIFs({Total Revenue}, {StatusRange 1}, "Completed")

    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.

  • Thanks Mark! 1 set step closer at least I am no longer getting UNPARSABLE, but now I got INCORRECT ARGUMENT SET.

    any tips?

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

    I edited my comment. It’s sumifs not sumif

    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.

  • That did the trick! Thanks so much Mark!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!