EXCEL中多条件求和、计数的4种方法

EXCEL中多条件求和、计数的

中多条件求和、计数的44种方法

EXCEL中多条件求和、计数的方法大致可归纳为4种：

⒈自动筛选法

⒉合并条件法

⒊数组公式法

⒋调用函数法

先打开上面的工作表，分别用这4种方法对同时满足“A2：A15区域为A，B2：B15区域为10，C2：C15区域为Ⅰ”条件的E2：E15区域进行求和、计数。

一、自动筛选法

利用EXCEL的自动筛选功能和分类汇总函数对工作表数据进行求和、计数。

①选中数据区域A1：E15，执行“数据→筛选→自动筛选”命令，进入“自动筛选”状态。

②选中E16单元格，输入分类汇总公式：=SUBTOTAL(9,E2:E15)，用于对求和列进行统计。

③点击“条件1”右侧的下拉按钮，在随后弹出的下拉列表中选择“A”；再点击“条件2”右侧的下拉按钮，在随后弹出的下拉列表中选择“10”；再点击“条件3”右侧的下拉按钮，在随后弹出的下拉列表中选择“Ⅰ”。

④符合条件的数据被筛选出来，合计自动出现在E16单元格中。

将SUBTOTAL(9,E2:E15)中的参数9改为2或3，可对符合条件的记录进行计数。

二、合并条件法

可将多个条件合并为一个条件，再利用条件求和函数、条件计数函数分别进行单条件求和、计数。

在D2单元格中输入合并公式：=A2&B2&C2，选择D2：D15，按Ctrl+D向下填充。

在E16单元格中输入条件求和公式：=SUMIF(D2:D15,"A10Ⅰ",E2:E15)

在E17单元格中输入条件计数公式：=COUNTIF(D2:D15,"A10Ⅰ")

三、数组公式法

利用数组公式进行多条件求和。

数组公式输入完成后，不能直接用“Enter”键进行确认，需要用“Ctrl+Shift+Enter”组合键进行确认。

确认完成后，公式两端会出现一对数组公式标志（一对大括号）。

在E16单元格中输入数组公式：

=SUM((A2:A15="A")*(B2:B15=10)*(C2:C15="Ⅰ")*E2:E15)或：

=SUM(IF((A2:A15="A")*(B2:B15=10)*(C2:C15="Ⅰ"),E2:E15))

输入完成后，按下“Ctrl+Shift+Enter”组合键确认公式即可。

即确认后的公式：{=SUM((A2:A15="A")*(B2:B15=10)*(C2:C15="Ⅰ")*E2:E15)}。

对于有“或”条件的，可用+来完成。如同时满足条件1=C，条件2=30，条件3=Ⅱ或Ⅲ，数组公式如下：

=SUM((A2:A15="C")*(B2:B15=30)*((C2:C15="Ⅱ")+(C2:C15="Ⅲ"))*E2:E15)或：

=SUM(IF((A2:A15="C")*(B2:B15=30)*((C2:C15="Ⅱ")+(C2:C15="Ⅲ")),E2:E15))

输入完成后，同样要按下“Ctrl+Shift+Enter”组合键。

四、调用函数法

调用SUMPRODUCT函数对数据进行求和、计数。

SUMPRODUCT函数：是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

在E16单元格中输入函数公式：

=SUMPRODUCT((A2:A15="A")*(B2:B15=10)*(C2:C15="Ⅰ")*E2:E15)

对于有“或”条件的，也可用+来完成。如同时满足条件1=C，条件2=30，条件3=Ⅱ或Ⅲ，该函数使用如下：=SUMPRODUCT((A2:A15="C")*(B2:B15=30)*((C2:C15="Ⅱ")+(C2:C15="Ⅲ"))*E2:E15)

也可用此函数来进行多条件计数：

=SUMPRODUCT((A2:A15="A")*(B2:B15=10)*(C2:C15="Ⅰ"))

★SUMPRODUCT是“返回乘积之和”函数，为什么可用来计数呢？

我们现以=SUMPRODUCT((A2:A4="A")*(B2:B4=10)*(C2:C4="Ⅰ"))为例来看他的计算过程：

先看每个单元格和三个条件的真假关系：

A2=A，条件为TRUE

A3=C，条件为FALSE（因为A3不等于A）

A4=B，条件为FALSE（因为A4不等于A）

B2=10，条件为TRUE

B3=30，条件为FALSE（因为B3不等于10）

B4=20，条件为FALSE（因为B4不等于10）

C2=Ⅰ，条件为TRUE

C3=Ⅲ，条件为FALSE（因为C3不等于Ⅰ）

C4=Ⅱ，条件为FALSE（因为C4不等于Ⅰ）

因此，原函数可变为：

=SUMPRODUCT((TRUE，FALSE，FALSE)*(TRUE，FALSE，FALSE)*(TRUE，FALSE，FALSE)在EXCEL中，TRUE 和FALSE分别用1和0表示。所以函数又变为：

=SUMPRODUCT((1，0，0)*(1，0，0)*(1，0，0))

然后接下来就是SUMPRODUCT的计算过程了：

=1*1*1+0*0*0+0*0*0=1

所以最后的结果等于1。

通过计算过程可以看出，对应位（即工作表的同一行或列，这里是同一行）只要有一个条件为0（即假，不符合条件），其乘积后就为0。

也就是说在前三条记录中，同时满足三种条件的只有1条记录。

同理，用SUMPRODUCT求和的计算过程如下：

=SUMPRODUCT((A2:A15="A")*(B2:B15=10)*(C2:C15="Ⅰ")*E2:E15)

=SUNPRODUCT((1，0，0，1，1，1，0，0，0，1，0，0，0，0)*

(1，0，0，0，1，1，0，0，0，0，0，0，0，0)*

(1，0，0，1，1，1，0，0，0，0，0，0，1，0)*

×（1，2，3，4，5，6，7，8，9，10,11，12，13，14))

--------------------------------------------------------

1＋0＋0＋0＋5＋6＋0＋0＋0＋0＋0＋0＋0＋0＝12

即最后的求和结果等于12。