# OzGrid

How to work with COUNTIF with multiple criteria

Category: [Excel]  Demo Available

### How to work with COUNTIF with multiple criteria

Requirement:

The user is looking for a formula solution to return a line count for one of three groups that meet one of four status in any one of four categories.

 A B C D E F 1 Name Cat1 Cat2 Cat3 Cat4 Group 2 Fred L C P F B 3 Harry I P C E S 4 Jim N S N N R 5 Stuart N N I P R 6

So, How many lines contain "R" in column F, that have either "C" or "P" or "I" or "F" in columns B or C or D or E?
The answer for the above example should be 1.

The formula the user would rework to query the same for Groups "B" and "S".

Solution:

=SUMPRODUCT((F2:F5="R")*(MMULT(--(ISNUMBER(MATCH(B2:E5,{"C","P","I","F"},0))),{1;1;1;1})>0))

Obtained from the OzGrid Help Forum.

Solution provided by NBVC.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

### Gallery

stars (0 Reviews)