Sumifs with multiple criteria

I am trying to sum the Open amount on another sheet if it meets 2 criteria. If the dispute box is checked in a column and if there is a 1 in the FM PO column. Here is are the formulas I have tried this far with no luck:

=SUMIF({Medxcel Fire Invoices Range 5}, OR(CONTAINS(1, {Medxcel Fire Invoices Range 4}), CONTAINS(1, {Medxcel Fire Invoices Range 1}))) - this formula is coming back as 0 and I know it is not 0.

=SUMIFS({Medxcel Fire Invoices Range 5}, {Medxcel Fire Invoices Range 4}, 1, {Medxcel Fire Invoices Range 1}, 1) - this formula is coming back as 0 and I know it is not 0.

Thank you!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Which range are you trying to sum?

Amy Broeker

Medxcel Fire Invoices Range 5

Paul Newcome

OK. The second SUMIFS should be working.

Which range is the checkbox and which range should contain the 1? How is the 1 populated in the source sheet?

Amy Broeker

The Medxcel Fire Invoices Range 5 range is a checkbox and it works by itself without the other range. {Medxcel Fire Invoices Range 1} is the number 1.

I just tried {Medxcel Fire Invoices Range 1} without the 1st range and it comes back as 0, so I am not sure if there is something wrong with that part of the formula.

Paul Newcome

Are you trying to sum or count?

You said earlier that Range 5 is the range you want to sum, but in your latest comment you say it is a checkbox.

What is Range 4?

How is the 1 populated in Range 1?

Amy Broeker

I figured out what the issue was. I have a contains formula in the {Medxcel Fire Invoices Range 1}, 1 column. When I deleted the formula on the sheet, this formula worked. I still need the formula in the column or it will create a manual process. Is it not possible to have a formula in a criteria column for SUMIFS?

Paul Newcome

It is possible. What was the formula you removed?

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.