Using Excel Multiple condition COUNTIF function

In my previous post, we have seen how to use single criteria Excel COUNTIF function. That works great  when you need to count a specific range of cells based on one condition.  Some times, there are situation we are required to count range of cells based on 2 or more conditions.  So let us have a quick look on the same.

Excel Multi condition count function  with AND:

The And criteria count function counts the cells if all the conditions are met.  The simple example for this is counting a range of cells which falls in between a numerical value. For example, counting cells that contains a value greater 0 and less than or equal to 20. Any cell that has positive value less than or equal to 20 will calculated in the Excel multi count with AND.For using this we need to use COUNTIF excel function or SUMPRODUCT function.

Syntax of COUNTIFS function:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Where
criteria_range1:  Required. The first range in which to evaluate the associated criteria.
criteria1 :  Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
criteria_range2, criteria2, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Note: COUNTIFS works only with Excel 2007 and higher versions. For using multiple condition count in earlier version of Excel we need to sumproduct.

Syntax for using SUMPRODUCT function as multicondition count:

As we have seen earlier, SUMPRODUCT function can be used to count range of cells based on multiple criteria.
Example for using Multicondition count in Excel: Below the sample data.

Counting only the sales of Apple for the month of Jan with value less than 250:
1. Using countifs function: =COUNTIFS(A2:A13,”Jan”,C2:C13,”Apple”, D2:D13,”2. Using the SUMPRODUCT function: =SUMPRODUCT((A2:A13=”Jan”)*(C2:C13=”Apple”)*(D2:D13
250>250>

Using Multiple condition count with OR:

To count cells by using an OR criteria, you should use multiple COUNTIFS functions.  For example, counting the number of instances of  8,6 and 5 in the range B2:B13.
=COUNTIF(B2:B13,8)+COUNTIF(B2:B13,6)+COUNTIF(B2:B13,5)

This can be achieved through COUNTIF function array formula of Excel.  The below Array formula returns the same result as that of above. As this is a array formula you need to enter the formula using Ctrl+Shift+Enter.
=SUM(COUNTIF(B2:B13,{8,6,5})

Note:  You need use flower bracket for 8,6,5 in the above example.