How to count rows that match multiple criteria

user_3512721 0
Viewed 362

I need a macro to count how many rows match some criteria.

In this example, the macros needs to be able to count where column2 is A or B and where column3 is X or W:

column1 column2 column3  
name1   A       X  
name2   B       X  
name3   A       W  
name4   C       X  
name5   A       Z  
name6   C       X  
name7   B       W

It should return 4, as name1, name2, name3, name7 are matching.

I have tried with COUNTIFS and array formulas but it seems that it's not possible without VBA.

Is this possible?

excel-formula excel
2 Answers

Use this Formula:

=SUMPRODUCT(COUNTIFS(B:B,{"A";"B"},C:C,{"X","W"}))

COUNTIFS can use Arrays, If more that one(two is the max) one must be Horizontal(using ,) and the other vertical(using ;)

One can use SUM() instead of SUMPRODUCT in this specific case, but if the arrays are replaced by ranges:

  1. If two Ranges one must be vertical and the other Horizontal. This can be accomplished by physically making the range that way or using TRANSPOSE() on one.

  2. If SUM() is used with a range it must be Array entered with Ctrl-Shift-Enter.

user_48515900

It is possible without VBA using the following formula:

=SUMPRODUCT((($B$2:$B$8="A")+($B$2:$B$8="B"))*(($C$2:$C$8="X")+ 
($C$2:$C$8="W")))
user_106916480
Related