Excel COUNTIFS函数可选地区筛选：清空下拉框时统计全地区

Got it, let's get this sorted out for you. The core idea you had with the IF wrapper is exactly the right approach—we just need to plug in the correct "no filter" formula for the empty dropdown case.

Option 1: Clear IF Nested Formula (Most Intuitive) #

This is the straightforward implementation of your initial idea, easy to read and debug later:

=IF(CHART!$J$1="", COUNTIF(Table1[SessionDate], "<" & $B3), COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], CHART!$J$1))

How it works:

• The outer IF checks if CHART!$J$1 is empty (your cleared dropdown state)
  • If yes: Use COUNTIF to count all rows where SessionDate is less than the date in B3 (no regional filter applied)
  • If no: Fall back to your original COUNTIFS formula that filters both date and selected district

Option 2: Single COUNTIFS with Dynamic Condition (More Concise) #

If you prefer a single formula without explicit nesting, you can dynamically adjust the district condition inside COUNTIFS:

=COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], IF(CHART!$J$1="", "*", CHART!$J$1))

Note for this option:

• The wildcard * matches any non-empty district value. If your District column has blank entries that you want to include when the dropdown is cleared, modify the condition to cover blanks too:

  =COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], IF(CHART!$J$1="", "*", CHART!$J$1)) + IF(CHART!$J$1="", COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], ""), 0)
  

  This adds a separate count for blank districts only when the dropdown is empty.

Either of these should work seamlessly with your existing setup—test the empty dropdown state to make sure it's counting all regions as expected!

内容的提问来源于stack exchange，提问作者Sean