Sumifs with AND and OR Criteria

Hi,


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

  1. The "Category" is "Banking"
  2. The "Owner" is "Resident"
  3. 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"

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this a go...


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

  • Automations 1
    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:

    1. Category is "Life Insurance"
    2. Owner is "Resident + Spouse"
    3. 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
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!