具有多个条件的SUMIFS和 [英] SUMIFS with multiple criteria and OR

问题描述

我已经看到很多关于SUMIFS的帖子，带有或，但是所有示例都具有一个标准和一个OR.通常使用这样的数组完成此操作:

I have seen plenty of posts for SUMIFS with or, but all the examples have a single criteria and an OR. This is usually done with an array like this:

=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))

我需要能够做到这一点，但是我需要它具有多个AND条件以及使用数组或类似方法的OR像这样:

I need to be able to do that, but I need it to have multiple AND criteria and the OR using array or something similar Like this:

=SUM(SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,{"red","blue"}))

但是它似乎不起作用.

请帮助！

推荐答案

我将通过示例向您展示它的工作原理

I'm showing you how it works with an example

从A1开始具有下表:

a|0|1
a|1|2
b|0|1
b|1|2
b|0|1
c|0|1
c|0|1

然后您尝试对第三列(C)中的值求和，其中第一列(A)为"a"或"b"(结果为7):

Then you try this to sum values in the third column (C) where first column (A) is either "a" or "b" (result is 7):

=SUM(SUMIFS(C1:C7, A1:A7, {"a","b"}))

然后尝试对第三列(C)中的值求和，其中第一列(A)为"a"或"b"，第二列(B)中的值为0(结果为3):

Then try this to sum values in the third column (C) where first column (A) is either "a" or "b" AND the value in second column (B) is 0 (result is 3):

=SUM(SUMIFS(C1:C7, B1:B7, 0, A1:A7, {"a","b"}))

请注意，由于这些公式包含一个数组，因此它们已经是数组公式.您无需通过使用[ctrl] + [shift] + [enter]来强制使用数组公式.

Note that since these formulas contain an array, they are already array formulas. You don't need to force an array formula by using [ctrl]+[shift]+[enter].

这篇关于具有多个条件的SUMIFS和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！