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"
Answers
-
Give this a go...
=SUMIFS({Total Amount}, {Category}, "Banking", {Owner}, "Resident", {Account Type}, OR(@cell = "Checking", @cell = "Savings"))
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!