如何在Excel的IF(AND...)嵌套公式中新增$B$2条件

问题背景 #

现有Excel公式逻辑如下：

• 外层以IF(Calculations!$B$1<>2023, ..., ...)作为判断
  • 当Calculations!$B$1≠2023时，基于$B$3、$B$4的下拉选项（可选"All"或其他值）嵌套IF，通过COUNTIFS统计'TAS'工作表中符合对应条件的行数
  • 当Calculations!$B$1=2023时，执行另一组逻辑类似但不含Calculations!$B$1条件的嵌套IF与COUNTIFS
    现在需要新增$B$2单元格（同样为下拉可选"All"或其他值）的判断逻辑：
• 若$B$2="All"，保留原COUNTIFS的基础条件
• 若$B$2≠"All"，需在COUNTIFS中追加'TAS'!$D$2:$D$999,"="&Calculations!$B$2的匹配条件

改造思路与实现 #

核心是在原公式的两个分支（B1≠2023和B1=2023）的COUNTIFS参数中，动态插入对$B$2的判断，实现条件的按需追加。

1. 针对Calculations!$B$1≠2023的分支改造 #

假设原分支的COUNTIFS基础结构如下（以$B$3、$B$4对应'TAS'表的X、Y列，B1对应Z列为例）：

COUNTIFS(
    'TAS'!$X:$X, IF($B$3="All", "*", $B$3),
    'TAS'!$Y:$Y, IF($B$4="All", "*", $B$4),
    'TAS'!$Z:$Z, Calculations!$B$1
)

加入$B$2的判断后，只需在COUNTIFS的最前面插入新的条件对：

COUNTIFS(
    'TAS'!$D$2:$D$999, IF($B$2="All", "*", Calculations!$B$2),
    'TAS'!$X:$X, IF($B$3="All", "*", $B$3),
    'TAS'!$Y:$Y, IF($B$4="All", "*", $B$4),
    'TAS'!$Z:$Z, Calculations!$B$1
)

2. 针对Calculations!$B$1=2023的分支改造 #

原分支不含B1的条件，改造逻辑一致，仅去掉对应条件对即可：

COUNTIFS(
    'TAS'!$D$2:$D$999, IF($B$2="All", "*", Calculations!$B$2),
    'TAS'!$X:$X, IF($B$3="All", "*", $B$3),
    'TAS'!$Y:$Y, IF($B$4="All", "*", $B$4)
)

完整整合公式 #

将两个分支用外层IF包裹，得到最终公式：

=IF(
    Calculations!$B$1<>2023,
    COUNTIFS(
        'TAS'!$D$2:$D$999, IF($B$2="All", "*", Calculations!$B$2),
        'TAS'!$X:$X, IF($B$3="All", "*", $B$3),
        'TAS'!$Y:$Y, IF($B$4="All", "*", $B$4),
        'TAS'!$Z:$Z, Calculations!$B$1
    ),
    COUNTIFS(
        'TAS'!$D$2:$D$999, IF($B$2="All", "*", Calculations!$B$2),
        'TAS'!$X:$X, IF($B$3="All", "*", $B$3),
        'TAS'!$Y:$Y, IF($B$4="All", "*", $B$4)
    )
)

注意事项 #

• 若原公式中$B$3、$B$4的判断逻辑不同（比如用""代替"*"匹配所有），保持原逻辑不变，仅插入$B$2的条件对即可
• 当$B$2="All"时，"*"匹配所有非空值，若需要包含空值，可改用IF($B$2="All", "<>", Calculations!$B$2)
• 请根据实际数据范围调整公式中的单元格区域（比如$X:$X替换为实际列范围）

内容的提问来源于stack exchange，提问作者A.R.