Sumifs with AND and OR Criteria

Hi,

I'm trying to collect a "Total Amount" if the following criteria is met:

The "Category" is "Banking"
The "Owner" is "Resident"
The "Account Type" is "Checking" or "Savings"

I figured out how to do Criteria 1 and 2. It's Criteria 3 that is getting me stuck. I know I can combine a few different Sumifs formulas together, or create a hidden helper column, but I REALLY don't want to.

Here's what I have so far. Please note that I'm referencing another sheet, hence the {} and not @row.

=SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, "Resident"

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Give this a go...

=SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, "Resident", {Account Type}, OR(@cell = "Checking", @cell = "Savings"))

Automations 1

Thank you @Paul Newcome that worked!!

I'm wondering if you can help me with the next part of my calculation? (It's the last Sumif in the below string that's not working...)

Add the Sum of half the "Cash Value" if:

Category is "Life Insurance"
Owner is "Resident + Spouse"
And the Face Value is blank

=SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, OR(@cell = "Resident", @cell = "Resident + Other")) + (SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, "Resident + Spouse") / 2) + SUMIFS({Cash Value}, {Category}, "Life Insurance", {Owner}, OR(@cell = "Resident", @cell = "Resident + Other"), {Face Value}, <>"") + (SUMIFS({Cash Value}, {Category}, "Life Insurance", {Owner}, "Resident + Spouse", {Face Value}, >1500 / 2)) + (SUMIFS({Cash Value}, {Category}, "Life Insurance", {Owner}, "Resident + Spouse", {Face Value}, <>"")) / 2

The strange thing is that when I just do the last sumifs on it's own, it works, but when i combine it with my other sumifs, it stops working.

Thanks!

Paul Newcome

It looks like there are a couple of misplaced parenthesis. Try this...

=SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, OR(@cell = "Resident", @cell = "Resident + Other")) + (SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, "Resident + Spouse") / 2) + SUMIFS({Cash Value}, {Category}, "Life Insurance", {Owner}, OR(@cell = "Resident", @cell = "Resident + Other"), {Face Value}, <>"") + (SUMIFS({Cash Value}, {Category}, "Life Insurance", {Owner}, "Resident + Spouse", {Face Value}, >1500) / 2) + (SUMIFS({Cash Value}, {Category}, "Life Insurance", {Owner}, "Resident + Spouse", {Face Value}, <>"") / 2)

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.