Expense Sum Formula

Options

I need to be able to calculate expenses per event and per expense type. I feel silly asking as I should know this but my brain just isn't calculating today.

I have tried many variations of the following and it's still Unparsable. I've also tried adding @cell. What am I missing?

=SUMIF([Event Title]:[Event Title], "MS CI Institute", AND ([Expense Type]:[Expense Type], "AV", "Facility Rental", [Payment Amount]:[Payment Amount]))

Tags:

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    Hello @Amy Shank

    Try this

    Formula in the Sheet Summary for MS CI Institute:

    • formula: =SUMIFS(Expense:Expense, [Event Type]:[Event Type], OR(@cell = "AV", @cell = "Facility Rental"), [Event Title]:[Event Title], "MS CI Institute")

    Hope this helps!

    Peggy

  • Amy Shank
    Options

    I was able to get this formula to work. However, it's not counting rows that have more than one expense type assigned to it.

    =SUMIFS([Payment Amount]:[Payment Amount], [Event Title]:[Event Title], "MS CI Institute", [Expense Type]:[Expense Type], OR(@cell = "Facility Rental", @cell = "AV"))

    In this instance the payment amount is tied to both "Facility Rental" and "AV" but the formula is returning a "0.00" value. How do I make sure it counts if more than one expense type is selected?

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @Amy Shank

    Try this -

    =SUMIFS([Payment Amount]:[Payment Amount], [Event Title]:[Event Title], "MS CI Institute", [Event Type]:[Event Type], OR(CONTAINS("AV", @cell), CONTAINS("Facility Rental", @cell)))

    Thanks, Peggy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!