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.

SUMIFS

I'm trying to make the formula in the first photo refer back to the amounts listed in the second photo and sum the amounts in Contract Terms if the Contract Type is either Flat Rent or Enforcement Only. I feel like I have the formula correct, but it's still showing $0, despite having data in the columns to sum. The sheets are saved, I confirmed that the Contract Terms don't have spaces before/after. I'm not sure what else to check.

Answers

  • ✭✭✭✭✭✭

    @Erica Shaeffer I think you are trying to use SUMIFS as if it used OR logic. In fact, SUMIFS is strictly AND logic, so all conditions must evaluate to true. Given that none of your rows have contract type of "either Flat Rent or Enforcement Only" the sum will always be zero.

    Maybe try:

    =SUMIF({Contract terms}, {Contract type}, "Flat Rent") + SUMIF({Contract terms}, {Contract type}, "Enforcement Only")

    dm

  • ✭✭✭✭✭✭

    You can use an OR in there to give it an option of values to choose from for a range.

    =SUMIFS({Contract terms}, {Contract type}, OR(@cell = "Flat Rent", @cell = "Enforcement Only"))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions