Creating dynamic Excel dashboards with drop-down lists is a fantastic way to let users filter data interactively. However, a common challenge arises when you want to add an "All" option to your drop-down menus. By default, Excel's COUNTIFS function expects exact matches or specific comparison operators, so selecting "All" often breaks the formula or returns zero results.

In this guide, we will explore three effective ways to update your COUNTIFS formula to seamlessly handle "All" selections in multi-criteria drop-downs.

The Scenario

Suppose you have a formula counting records based on multiple criteria like Delivered Date (Week), Region, Type, and Case Owner:

=COUNTIFS(adi_data[Delivered Date (Week)], E3, 
          adi_data[Region], $F$2, 
          adi_data[Type], A$4, 
          adi_data[Case Owner], C$4)

When cell A$4 (Type) or C$4 (Case Owner) is set to "All", you want Excel to ignore that specific criteria filter and count all matching records for the remaining filters.

Solution 1: Using Wildcards with IF (Simplest for Text Data)

If your Type and Case Owner fields contain text values, the easiest approach is to replace "All" with the asterisk wildcard ("*") inside an inline IF function. The asterisk matches any text sequence.

Updated Formula:

=COUNTIFS(adi_data[Delivered Date (Week)], E3,
          adi_data[Region], $F$2,
          adi_data[Type], IF(A$4="All", "*", A$4),
          adi_data[Case Owner], IF(C$4="All", "*", C$4))

How It Works:

  • IF(A$4="All", "*", A$4) checks if the user selected "All".
  • If TRUE, it uses "*" as the criterion, instructing COUNTIFS to match any text entry in that column.
  • If FALSE, it evaluates to the specific selected value in A$4.

Note: This method works best for text columns. If your column contains numbers or blank cells that need to be included, consider Solution 2 or 3.

Solution 2: Modern Dynamic Arrays with FILTER and ROWS (Excel 365 & 2021)

If you are using Microsoft 365 or Excel 2021, boolean logic with FILTER and ROWS provides a cleaner and more powerful solution that works for text, numbers, and dates alike.

Updated Formula:

=LET(
  filtered, FILTER(
    adi_data,
    (adi_data[Delivered Date (Week)] = E3) *
    (adi_data[Region] = $F$2) *
    ((A$4 = "All") + (adi_data[Type] = A$4)) *
    ((C$4 = "All") + (adi_data[Case Owner] = C$4),
    ""
  ),
  IF(ISARRAY(filtered), ROWS(filtered), 0)
)

How It Works:

  • Addition (+) acts as OR logic: (A$4 = "All") + (adi_data[Type] = A$4) evaluates to TRUE if either A$4 is "All" OR the row matches A$4.
  • Multiplication (*) acts as AND logic: All conditions must be met for a row to pass through the filter.
  • ROWS(...) counts the number of filtered records.

Solution 3: The SUMPRODUCT Alternative (All Excel Versions)

If you need numerical compatibility without relying on wildcards and need your workbook to work on older Excel versions (2019 and earlier), SUMPRODUCT is the go-to alternative.

Updated Formula:

=SUMPRODUCT(
  (adi_data[Delivered Date (Week)] = E3) *
  (adi_data[Region] = $F$2) *
  ((A$4 = "All") + (adi_data[Type] = A$4) > 0) *
  ((C$4 = "All") + (adi_data[Case Owner] = C$4) > 0)
)

Which Method Should You Choose?

  • Use Solution 1 (Wildcard IF) if your data columns contain purely text criteria and you want to keep using standard COUNTIFS functions.
  • Use Solution 2 (FILTER) if you are using Excel 365 and want the most robust logic for text, numbers, or dates.
  • Use Solution 3 (SUMPRODUCT) if you need backwards compatibility across older versions of Microsoft Excel.