How to Skip Parameters in Excel Formulas Based on Cell Values

A user on a popular Excel community forum recently asked: “I’m building a dashboard where I can filter sales data by ‘Region’ and ‘Product’. However, if I leave the ‘Region’ cell blank, I want the formula to show everything instead of returning zero results. How do I tell Excel to skip a criteria parameter based on a cell value?”

This is a common hurdle when building dynamic reports. Excel functions like SUMIFS, COUNTIFS, and FILTER usually require an exact match, but you can “bypass” these parameters using a bit of Boolean logic.

The Minimal Working Example

If you are using Microsoft 365 or Excel 2021+, the most efficient way to handle this is with the FILTER function.

The Scenario:

• Data Range: A2:C100 (Columns: Date, Category, Amount)
• Criteria Cell: E1 (The category you want to filter by)

The Formula:

=FILTER(A2:C100, (B2:B100 = E1) + (E1 = ""), "No results found")

Tested on Excel for Microsoft 365 (January 2024).

---

Why This Works: The “Plus” Logic

In Excel formulas, the + symbol acts as an OR statement when dealing with arrays.

1. (B2:B100 = E1): This checks if the data matches your criteria cell. If E1 is “Electronics”, it returns TRUE for those rows.
2. (E1 = “”): This checks if your criteria cell is empty. If it is empty, this returns TRUE for every single row in the dataset.
3. The Result: By adding them together, if E1 is blank, every row becomes TRUE (1), effectively “skipping” the filter.

---

Comparison of Methods

Depending on your version of Excel or your specific goal (summing vs. listing rows), you might choose a different approach.

Method	Best For	Version	Logic Type
FILTER with Boolean	Returning a list of rows	M365 / 2021	(Criteria) + (Cell="")
SUMIFS with Wildcards	Summing text-based data	2010+	IF(E1="", "*", E1)
IF/IFS Switch	Simple, small datasets	All	IF(E1="", SUM(A:A), SUMIFS(...))

---

Adapting to SUMIFS (Legacy Excel Versions)

If you aren’t using Microsoft 365, or if you simply need a total sum rather than a list, you can use a wildcard trick within a SUMIFS formula. Note that this only works for text fields.

Formula for Text Criteria:

=SUMIFS(C2:C100, B2:B100, IF(E1="", "*", E1))

Tested on Excel 2019 and 2021.

Explanation: The IF statement inside the formula checks if E1 is blank. If it is, it inserts a wildcard (*), which tells Excel to “match everything.” If E1 has a value, it uses that specific value.

---

Common Traps and Troubleshooting

1. What if my criteria cell says “All” instead of being blank?

If you prefer your dropdown menu to say “All” rather than leaving it empty, simply update the logic in the FILTER formula: =FILTER(A2:C100, (B2:B100 = E1) + (E1 = "All"))

2. What if I am filtering by Numbers?

The wildcard (*) trick does not work for numbers. If you are filtering by a Year or a Price, you must use the Boolean addition method: =FILTER(A2:C100, (Price_Column = E1) + (E1 = ""))

3. How do I handle multiple “Skippable” parameters?

If you have multiple filters (e.g., Region, Category, and Manager), you wrap each “OR” statement in parentheses and multiply them together (which acts as AND logic):

=FILTER(A2:C100, ((B2:B100 = E1) + (E1 = "")) * ((C2:C100 = F1) + (F1 = "")))

In this version, if E1 is blank AND F1 is blank, the formula will return the entire table. If you fill one out, it filters by that one and skips the other.

4. Can I automate this with VBA?

While you can write a Macro to update filters, it is generally discouraged for simple reporting. Modern Excel functions like FILTER are “spillable,” meaning they update instantly without the need for a script or a Data > Refresh action. Stick to formulas unless your dataset exceeds 100,000+ rows where calculation speed might become an issue.