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"))

Find more posts tagged with

Formulas

Accepted answers

Heather Duff

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!

Best,

Heather

Paul Newcome

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"))

All comments

Heather Duff

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!

Best,

Heather

Paul Newcome

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"))

Ryan Williams

Thanks for the help! @cell worked like a charm. <> seems to work well too

Thank you both for your help.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.