Using NOT and OR functions in Sumifs


I am trying to create a sumifs function that sums up all of the expenses in a second sheet if they are not labeled "travel". I attempted it with an all-inclusive OR function and then tried it with the NOT function and neither seem to work. It seems that they need to look at cells specifically within the sheet and can't look back at the cross referenced sheet. What am I missing from the use of NOT or OR, or some other formula component?

Here is what I have.

=SUMIFS({CashExpenses}, {CashExpensesMonth}, [Month or Quarter]@row, {CashExpensesCode}, [Code]@row, {CashExpensesCategory}, NOT("Travel"))


Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Ryan,

    Try using an @cell reference.

    =SUMIFS({CashExpenses}, {CashExpensesMonth}, [Month or Quarter]@row, {CashExpensesCode}, [Code]@row, {CashExpensesCategory}, NOT(@cell="Travel"))

    Let me know if it works!



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I agree with @Heather D. Try the @cell reference in the NOT function.

    I personally use <> which is "not equal to" in place of the NOT statement simply because of those pesky parenthesis.

    =SUMIFS({CashExpenses}, {CashExpensesMonth}, [Month or Quarter]@row, {CashExpensesCode}, [Code]@row, {CashExpensesCategory}, @cell <> "Travel")

    For other cases if you do in fact want to specify (for example) two different criteria for the same range with an OR statement, you would use the OR in the criteria portion with @cell references.

    =SUMIFS({CashExpenses}, {CashExpensesMonth}, [Month or Quarter]@row, {CashExpensesCode}, [Code]@row, {CashExpensesCategory}, OR(@cell = "This", @cell = "That"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!