Is there a way to set up a SUMIFS formula that will ignore the search for criteria if needed?
I need to set up a SUMIFS formula based on 5 pieces of criteria
- Business
- Category
- Brand
- Product Name
- Customer
However, is it possible to set up formula that would ignore the need to search for all 5 criteria if any of the criteria are blank? For example, if I wanted to the sum of all products for a specific customer regardless of business, category, brand, or product name and those sections are left blank, can the formula be set up to ignore the need to verify based on that criteria?
EDIT: Screenshot added for reference. Withe current formula, it will return a value based on all 5 pieces of criteria being met, but if I intentionally leave criteria blank, I want it to ignore summing based on that criteria and instead only focus on the criteria that are not blank. Is this possible?
Answers
-
Are you able to provide some screenshots for context?
-
Screenshot added to initial post, thanks!
-
Screenshot added to initial post, thanks!
-
You would use IF statements for your criteria. It would look something like this:
=SUMIFS({Range to sum}, {Criteria Range}, IF([Column Name]1 <> "", @cell = [Column Name]1, OR(@cell = "", @cell <> "")), …………….
Basically you tell the formula to use the criteria if there is something in the cell, otherwise pull in everything for the range.
-
Thank you so much for your help.
I am struggling a bit with this formula when I add the second argument. When I just have it searching for B2B/B2C it works, if the dropdown cell is blank it pulls nothing, but if the dropdown cell says B2C, it pulls all of the data with B2C. However when I add a second argument for it to search such as category, it says invalid operation.
Basically, I want the formula to return a sum based on the filled in criteria under the dropdown column. So if only Brand is filled in it will sum up everything with that Brand, but if Brand + Category + Customer are filled in, it will only sum up based on those 3 criteria.
-
Try using the @cell references inside of the OR function as I had it in my example.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!