我已经看到很多关于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和
发布评论