CODEWITHSUNDEEP
excelexcel-formula
Gokh
Gokh

Reputation: 125

How to count rows that match multiple criteria

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?

Upvotes: 3

Views: 470

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 5

F.C.
F.C.

Reputation: 93

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")))

Upvotes: 2

Related Questions