数组作为Excel中的条件COUNTIFS函数，混合AND和OR [英] Array as criteria in Excels COUNTIFS function, mixing AND and OR

问题描述

我已经在谷歌搜索了一段时间，似乎不能让它上班。我使用Excel 2010，并希望对AND和OR运算符进行计数。

我想做的是这样的一个

  COUNTIFS（$ A：$ A，string1，$ B：$ B，string2，$ C：$ C，{stringA stringB，stringC}）
  

这意味着在COUNTIFS函数中混合AND和OR运算符。 col A和col B必须匹配字符串条件，但col C只能匹配给定为数组的数组中的一个值。在colA和colB上匹配，并在col C中的一个数组值。

不同的方法是为数组中的每个值创建一个COUNTIFS函数，如

  COUNTIFS（$ A：$ A，string1，$ B：$ B，string2，$ C：$ C，stringA + COUNTIFS（$ A：$ A，string1，$ B：$ B，string2，$ C：$ C，stringB）+ COUNTIFS（$ A：$ A，string1，$ B ：$ B，string2，$ C：$ C，stringC）
  

很多重复的代码，并且我的错误！逻辑解决方案是将数组作为列C的标准传递。另外我的数组包含三个以上的值...

当我在Excel中这样做时，公式是被接受并且计数了几行，但结果是低的。这不是我期望的结果。

任何Excel-Pro可以告诉我，如果这是可能的话它会救我很多工作！
谢谢！

解决方案

您可以使用

  = SUM（COUNTIFS（$ A：$ A 字符串1，$ B：$ B， 字符串2，$ C：$ C，{ stringA， stringB，stringC }））
  

您还可以使用类似的数组构造与副产品

  = SUMPRODUCT（（$ A：$ A =string1）*（$ B：$ B =string2）*（$ C：$ C = {stringA，stringB，stringC}））
  

I have been googling this for a while and can´t seem to get it to work. I use Excel 2010 and want to count rows on a mix of AND and OR operators.

What I want to do is something like this

COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,{"stringA","stringB","stringC"})

This means mixing both AND and OR operator in the COUNTIFS function. Col A and col B must match the string criteria but col C must only match one of the values in the array given as criteria. Match on colA AND colB AND on one array value in col C.

A different approach would be to create one COUNTIFS function for each value in the array like

COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,"stringA") + COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,"stringB") + COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,"stringC")

This however is a lot of duplicate code and that bugs me! The logic solution would be to pass an array as criteria for column C. Also my array contains more then three values...

When I do this in Excel the formula is accepted and a few rows are counted but the results are way to low. It is not the result I´m expecting.

Any Excel-Pro out there that can tell me if this is possible? It would save me a lot of work! Thanks!

解决方案

you can use

=SUM(COUNTIFS($A:$A,"string1",$B:$B,"string2",$C:$C,{"stringA","stringB","stringC"}))

you can also use a similar array construction with sumproduct

=SUMPRODUCT(($A:$A="string1")*($B:$B="string2")*($C:$C={"stringA","stringB,"stringC"}))

这篇关于数组作为Excel中的条件COUNTIFS函数，混合AND和OR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！