• 首页
• Office
• 数组作为Excel中的条件COUNTIFS函数，混合AND和OR

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

### 问题描述

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

` ` 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） ` `

` ` = 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"}))
``````