How to use multiple criteria using Countifs function in Excel

Countifs function in Excel allows you to count number of cells specified by a given set of more than one conditions or criteria. In this guide we will discuss how to use multiple criteria using Countifs function in Excel.

During working on Excel sheet sometime we need to apply multiple criteria for counting the values. It will be complex task and chances of wrong result is too much. In that situation you must know an easy way which helps you to know how to use countifs with multiple criteria and OR logic.

How to use multiple criteria using Countifs function in Excel

Excel is an important utility application software which allows you to solve complex situations and get accurate results. If you have better command on the functions then you can easily and quickly get the result of any complex criteria. In this guide we have to know how to apply multiple criteria with countifs function in Excel. Before start this process first we must know about the countifs function.

Countifs function is allows you to quickly count the number of cells within a range or cells or any array that match the multiple condition.

Syntax of Countifs: COUNTIFS(range1, criteria1,range2, criteria2…)

Range1, range2, … : You can give up to 1 to 127 ranges which include any number, name, array or reference that contain any kind of number, but blank and text values are ignored.

Criteria1, criteria2, … You can give up to 1 to 127 conditions in which you can set any number, expression, cell address, or any text value that define which cells will be counted

For eg: You have a worksheet in which there are lot of book names. Now you want to know how many quantity of “Tally” & “Excel” books in your store. In that situation you must have to use countifs function in Excel to get the correct result, have a look.

How to use multiple criteria using Countifs function in Excel

Step 1: Prepare the following worksheet to start to use multiple criteria using Countifs function in Excel.

Step 2: There are two ways from which you can easily get the result using multiple criteria with countifs function in Excel.

First Method:
If you want to use multiple countifs then type given countifs function in cell H3 =COUNTIFS(B2:B11,”TALLY”,C2:C11,”>10″)+COUNTIFS(B2:B11,”EXCEL”,C2:C11,”>10″).

Second Method:
If you don’t want to use multiple countifs then you have to apply given countifs function in cell H4 =SUM(COUNTIFS(B2:B11,{“TALLY”,”EXCEL”},C2:C11,”>10″))

Both function shows same result in your active worksheet. You can choose any or both methods to get the result as per the given situation in your active worksheet.

Hopefully after reading how to use multiple criteria using Countifs function in Excel guide will be helpful for you. If you feel that we missed out any thing then please let me know through comment box.

Leave a Reply

Your email address will not be published. Required fields are marked *