Excel COUNTIFS函数应用：求和后计算占比的公式问题

需求说明 #

• 基于CSH Overview工作表D列的地址，统计CSH Test工作表中C列地址匹配且W:Z列状态为New或Installed的设备总数
• 在CSH Overview工作表J列计算两种状态的占比（当前预期结果为50%，说明New与Installed数量相等）
• 现有公式要么返回#DIV/0!/#VALUE!错误，要么结果错误（如得出1%，实际应为50%）

错误公式分析 #

1. 返回#VALUE!的公式 #

=COUNTIFS('CSH Overview'!C:C,'CSH Overview'!D10,'CSH Test'!W:Z,"Installed")

问题：COUNTIFS要求所有条件区域的行列数完全一致，这里'CSH Overview'!C:C（单列）与'CSH Test'!W:Z（4列）区域大小不匹配，触发#VALUE!错误。

2. 逻辑错误的公式 #

=COUNTIF('CSH Test'!C:C,D10) + IF(SUMIF('CSH Test'!W1:Z1016, "Installed") > 0, COUNTIF('CSH Test'!W1:Z1016, "Installed") / (SUMIF('CSH Test'!W1:Z1016, "Installed") + SUMIF('CSH Test'!W1:Z1016, "New")), 0)
=COUNTIF('CSH Test'!C:C,'CSH Overview'!D10) + COUNTIF('CSH Test'!W1:Z1016,"Installed") / (SUMIF('CSH Test'!W1:Z1016,"Installed") + SUMIF('CSH Test'!W1:Z1016,"New"))
=COUNTIF('CSH Test'!C:C,'CSH Overview'!D10) + SUMIF('CSH Test'!W1:Z1016,"Installed") + SUMIF('CSH Test'!W1:Z1016,"New")
=COUNTIF('CSH Test'!C:C,'CSH Overview'!D10) + SUMIF('CSH Test'!W1:Z1016,"Installed") + SUMIF('CSH Test'!W1:Z1016,"New") - SUMIF('CSH Test'!W1:Z1016,"Installed")*100/100

问题：未关联「地址匹配」与「状态为New/Installed」两个条件，单纯将地址匹配行数与全局状态统计数相加，逻辑完全偏离需求。

3. 结果错误的公式 #

=IFERROR(SUM(COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!W:W,"Installed") +
COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!X:X,"Installed") +
COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!Y:Y,"Installed") +
COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!Z:Z,"Installed")) /
SUM(COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!W:W,"New") +
COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!X:X,"New") +
COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!Y:Y,"New") +
COUNTIFS('CSH Test'!C:C,[@Address],'CSH Test'!Z:Z,"New")*100)/100,"")

问题：括号位置错误，*100被包含在分母的SUM范围内，导致分母被放大100倍，后续再除以100后结果缩小为正确值的1/100，因此得出1%的错误结果。

正确公式 #

表格格式（[@Address]引用） #

计算Installed占总设备数的比例：

=IFERROR(
    SUM(COUNTIFS('CSH Test'!C:C, [@Address], 'CSH Test'!W:Z, "Installed")) /
    SUM(COUNTIFS('CSH Test'!C:C, [@Address], 'CSH Test'!W:Z, {"New","Installed"})),
    0
)

计算New占总设备数的比例，只需把第一个条件中的"Installed"换成"New"即可。

普通单元格引用（如D10） #

=IFERROR(
    SUM(COUNTIFS('CSH Test'!C:C, D10, 'CSH Test'!W:Z, "Installed")) /
    SUM(COUNTIFS('CSH Test'!C:C, D10, 'CSH Test'!W:Z, {"New","Installed"})),
    0
)

公式说明 #

1. SUM(COUNTIFS('CSH Test'!C:C, [@Address], 'CSH Test'!W:Z, "Installed"))：统计匹配当前地址、且W:Z列中状态为Installed的设备总数（多列统计结果用SUM求和）
2. SUM(COUNTIFS(...,"New","Installed"))：用数组{"New","Installed"}一次性统计两种状态的匹配总数，SUM求和得到总设备数
3. IFERROR(...,0)：避免总设备数为0时返回#DIV/0!错误，返回0替代

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