SUMIFS using Dropdown

Hello!

I have what is probably a simple question my brain is just not comprehending properly.

I need to SUMIFS from a sheet that matches some specific criteria but one of the fields I am using against needs to calculate a total if one dropdown field matches ANY ONE of these 3 items that is in it to be chosen… Capitol National Bank choices, Wire Transfer, OR Check along with some other criterion.

Currently, the formula reads as such and all of the existing fields are still relevant.

=SUMIFS({FY26 - Trip Detail PurchAMT}, {FY26 - Trip Detail Trip Name}, "Switzerland Mission", {FY26 - Trip Detail Expense Type}, "Airfare")

I am thinking its a combination of HAS and OR expressions but I don't have nearly enough experience with using them that I can comprehend where they go and how they need to be oriented to accomplish my work.

Any help is so greatly appreciated! Happy Holidays!😊

Find more posts tagged with

Formulas

Cross Sheet Reference

Accepted answers

Paul Newcome

Give this a try:

=SUMIFS({FY26 - Trip Detail PurchAMT}, {FY26 - Trip Detail Reimbursement}, 0, {FY26 - Trip Detail Trip Name}, "Switzerland Mission", {FY26 - Trip Detail Expense Type}, "Airfare", {FY26 - Trip Detail PO/CC}, OR(CONTAINS("Capitol National Bank",@cell), CONTAINS("Wire Transfer", @cell), CONTAINS("Check", @cell)))

All comments

jjg279

Howdy @degarmod - you are correct, an OR statement is what you need. Using your formula above as a starting point I think something like this should work:

=SUMIFS({FY26 - Trip Detail PurchAMT}, {FY26 - Trip Detail Trip Name}, "Switzerland Mission", {FY26 - Trip Detail Expense Type}, "Airfare", {PO or CC Range}, OR(@cell ="Capitol National Bank", @cell ="Wire Transfer", @cell ="Check") )

Alternatively if you were looking for the words "Capitol National Bank" in any option you could use this to combine the OR with a CONTAINS:

=SUMIFS({FY26 - Trip Detail PurchAMT}, {FY26 - Trip Detail Trip Name}, "Switzerland Mission", {FY26 - Trip Detail Expense Type}, "Airfare", {PO or CC Range}, OR(CONTAINS("Capitol National Bank", @cell), @cell ="Wire Transfer", @cell ="Check") )

degarmod

I'm trying the looking for the capitol national bank but am struggling since this is a cross sheet reference…. tried it both ways with calling out the sheet each time and just using @cell but not seeing any change. Returns as UNPARSEABLE.

=SUMIFS({FY26 - Trip Detail PurchAMT}, , {FY26 - Trip Detail Reimbursement}, 0, {FY26 - Trip Detail Trip Name}, "Switzerland Mission", {FY26 - Trip Detail Expense Type}, "Airfare", {FY26 - Trip Detail PO/CC}, OR(CONTAINS("Capitol National Bank",{FY26 - Trip Detail PO/CC}@cell ), {FY26 - Trip Detail PO/CC}@cell ="Wire Transfer", {FY26 - Trip Detail PO/CC}@cell ="Check"))

Paul Newcome

Give this a try:

degarmod

This formula appears to have done the trick…just need to work with the user on a tweak to adjust getting duplicative data since we're using a lot of the same fields. Thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.