It is shown in this lesson Excel COUNTIFS function – Count cells with multiple criteria – AND logic and OR logic, using logic that is both “AND” & “OR” in nature. In this section, you’ll discover a variety of examples for various data types, including integers, dates, text, wildcard characters, non-blank cells, and others.
When it comes to Excel functions, COUNTIFS and COUNTIF are the ones that are most often confused since they appear so similar, and both are intended to count cells depending on the criteria that have been set. The distinction is that COUNTIF is designed for measuring cells with a single condition in a single range. Still, COUNTIFS is intended for evaluating multiple criteria in the same or various capacities at the same or different times.
This lesson aims to illustrate several ways and assist you in selecting the most effective formula for each specific work you encounter.
Before Using Countifs
Counting cells in a single range with a single condition and cells in multiple ranges with multiple conditions is possible using the COUNTIFS function in Excel. If the latter is true, only those cells that satisfy all given requirements are included in the total number of cells.
It is required that each successive range have the same number of rows and columns as the first range (as specified by the criteria range1 parameter).
Ranges that are both contiguous and non-contiguous are permitted.
A reference to an empty cell in the criteria is treated as a zero value by the COUNTIFS function (0).
In criterion, you may use the wildcard characters asterisk (*) and question mark (?) to represent any character. For further information, please see this example.
Countifs Formula With Multiple Criteria Or Logic
In this case, the COUNTIFS function in Excel is meant to count just those cells for which all of the provided criteria are TRUE, making it the most straightforward situation. Because the AND function in Excel operates in this manner, we refer to it as the AND logic.
Consider the following scenario: you have a product list similar to the one displayed in the picture below. You would want to count the number of things that are currently in stock (the value in column B is higher than 0), but have not yet been sold (value in column C is equal to 0).
Step 1: Open a Microsoft Excel spreadsheet and enter the data you need to count. We will take the unsold count as the main criteria in our data. Our formula will calculate how many products are unsold.
Step 2: Enter the formula “=COUNTIFS(B2:B7,”>0″, C2:C7,”=0″)” into your desired cell. When you apply the formula, change the data range B2:B7 and C2:C7 according to your column and data range, and the formula will work perfectly.
Step 3: Press enter to apply the formula, and it will count the data for unsold items. You can even change the “>0” and “=0” according to your required number to make the calculations.
Using Countifs in the same Criteria
When you wish to count things that meet the same criteria, you must still provide each criteria range/criteria pair on its basis. For example, the following is the correct formula for counting items with a value of 0 in both column B and column C. The formula you will need is “=COUNTIFS($B$2:$B$5,”=0″, $C$2:$C$5,”=0″)”
Step 1: Open up an excel spreadsheet with the data you need to apply the formula; this formula is helpful in finding out items that are not in stock or finished.
Step 2: Insert the formula “=COUNTIFS($B$2:$B$5,”=0″, $C$2:$C$5,”=0″)” into your desired cell range. Here $B$2:$B$5 is the cell start and end range, and $C$2:$C$5 is the comparing column cell range. Change it according to your preferences. Also, you can change the “0” amounts as you require to change the formula.
Step 3: After changing the formula to your need, just press enter, and the results will be shown.
Count Cell With Multiple Logic Or Criteria
The COUNTIFS function is meant to operate in this manner, as seen in the examples above. As you can see, counting cells that fulfill all stated conditions are straightforward.
However, the question arises if you wish to count cells for which at least one of the above requirements is TRUE. In other words, if you want to count cells based on the OR logic. The most straightforward method is, to sum up, numerous COUNTIF formulae\ or to use a SUM COUNTIFS formula in conjunction with an array constant to get the desired result. You will need the formula “=COUNTIF($C$2:$C$11,”Cancelled”) + COUNTIF($C$2:$C$11,”Pending”)”
Step 1: Open the excel datasheet you need to analyze and enter the formula; you can change the words inside the double quotation to your desired words that you need to check the data for. You can also change the cell ranges to your requirement.
Step 2: Press enter to get the results; we are checking for On hold and Cancelled deliveries, and in our data set, the number is 3, as you can see from the screenshot.
Use Countifs With Array Constants
When you must analyze many criteria, the technique described above is not the greatest option since your formula would become too large. Use an array constant to store all of your criteria. Then, provide that array to the criteria parameter of the COUNTIFS function to conduct the same computations in a more compact formula. To get the overall count, embed COUNTIFS inside the array. All you will need is the formula “=SUM(COUNTIFS($C$2:$C$7, {“On Hold”, “Cancelled”, “En route”}))” . This method is preferable when you have a lot of logic.
Step 1: Open the spreadsheet you need to work on with this formula and enter the formula; change the logic and the cell range according to your data set. You can add more or less than the logic we have used.
Step 2: After you have customized the cell range and the logic, press enter to see the results. See the picture above; our logic returned several 5, which means 5 cells contain the logic.
Important Notes About Countif
COUNTIFS is a function that is available in Microsoft Excel 2000.
#VALUE! Error – Occurs when the lengths of the criteria range arrays are not the same as each other.
The standards arguments that have been supplied are text strings longer than 255 characters in length.
Each new range should have the same number of rows and columns as the criteria range1 parameter. However, they are not required to be close to one another in the same row or column.
Conclusion:
You know the Excel COUNTIFS function – Count cells with multiple criteria – AND logic and OR logic. These functions will make your life a lot easier. You will be able to piece precious data from large spreadsheets within minutes with these Counties formulas.