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.
- 3 Ways to find duplicate values on same worksheet – Excel
- Advanced VLookup trick with Data Validation in Excel
- How to Find Duplicates with Pivot Table in Excel
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.
Step 1: Prepare the following worksheet to start to use multiple criteria using Countifs function in Excel.
- How to Sum or Count cells based on background color in Excel
- How to Apply Alternate Color Shading Between Rows 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 formula 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.