Is there a way to set up a SUMIFS formula that will ignore the search for criteria if needed?

cmcevoy

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?

Paul Newcome

Are you able to provide some screenshots for context?

cmcevoy

Screenshot added to initial post, thanks!

cmcevoy

https://community.smartsheet.com/discussion/comment/459333#Comment_459333

Screenshot added to initial post, thanks!

Paul Newcome

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.

cmcevoy

https://community.smartsheet.com/discussion/comment/459365#Comment_459365

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.

Paul Newcome

Try using the @cell references inside of the OR function as I had it in my example.