The COUNTIF function in Excel is a valuable tool for conditional counting and data analysis. However, its limitation in handling multiple criteria poses a challenge. This can lead to inefficiency and hinder productivity. In this article, we present two effective methods to efficiently count with multiple criteria, providing solutions to this common problem.
The First way：Basic usage of the COUNTIFS function
Consider the following table. Now we want to know how many different kinds of meals are manufactured in Thailand and have a sales volume of more than 5,000.We'd want to think about two criteria.
One need is that the sales volume exceed 5,000, and the other is that the location of origin be in Thailand.As a result, we may employ the COUNTIFS function.
To find the COUNTIFS function, select the cell, go to the Formulas tab, and then click the Insert Function button.
Range1 is the data region to be counted, and criteria1 is the needed criterion. Our initial requirement is a sales volume of at over 5,000. Because this requirement falls inside the cell range D2:D13 , we should enter D2:D in range 1.Then, in criteria1, insert our
criterion >5000.
The location of origin in Thailand is the second needed criterion. Similarly, the table shows that our requirements are in the cell range C2:C13. As a result, we should enter C2:C13 in range2.
Because the text content of cell C4 is Thailand, enter the criteria C4 in criteria2. Then press the OK button. We now know that there are four Thai delicacies with sales figures above 5000.
This is the most simple application of the COUNTIFS function. It may be used to count data from several criteria. We might study how to utilize WPS Office Spreadsheet online in WPS Academy to become office excel experts.
The Second way：COUNTIFS formula with multiple criteria
Below are some formula examples that show how to utilize the COUNTIFS and COUNTIF functions in Excel to assess multiple conditions.
Formula 1. COUNTIFS formula with multiple criteria
Assume you have a product list similar to the one shown in the picture below. You wish to count the number of things that are in stock (value in column B larger than 0) but have not yet been sold (value in column C equals 0).
The following formula can be used to complete the task:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
And the total is 3 ( Oyster, Onion, Sake )
Formula 2. COUNTIFS formula with two criteria
When counting items with identical criteria, you must still specify each criteria_range / criteria pair separately.
For example, below is the correct formula for counting items with 0 in both column B and column C:
=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")
Because only "Pepper" has a "0" value in both columns, this COUNTIFS algorithm yields 1.
A shorter formula with a single criteria_range, such as =COUNTIFS(B2:C7,"=0"), would get a different result - the total number of cells in the range B2:C7 having a zero (in this example, 5).
FAQs
1.What is the maximum number of criteria that COUNTIF can handle?
The COUNTIF function in Excel can handle up to 127 range/criteria pairs in your formulas.
2.How do I count unique values with multiple criteria using COUNTIF?
To count unique numbers in Excel, use the SUM and COUNTIF tools together. This combined formula is written as = SUM(IF(1/COUNTIF(data, data)=1,1,0)). The COUNTIF algorithm here counts how many times each value in the range appears. The resultant array is {1;2;1;1;1;1;1}.
3.How can I count values based on dates or time ranges?
To count values based on dates or time ranges in Excel, you can use the COUNTIFS function along with specific criteria for the date range.
Here is the COUNTIFS formula entered in field F5 to calculate total units sold for the date range:
=COUNTIFS($A$2:$A$9,">=" & $F$2, $A$2:$A$9, "
Summary
In conclusion, the COUNTIF function in Excel is a valuable tool for conditional counting and data analysis. However, its limitation in handling multiple criteria can be a challenge. To address this, we have explored two efficient ways to use the COUNTIF function, including the use of COUNTIFS with multiple criteria.
By implementing these methods, users can overcome the limitations of COUNTIF and achieve more accurate and efficient counting results. Emphasizing the importance of learning advanced Excel functions, such as COUNTIFS, can further enhance productivity. Additionally, considering alternative office suites like WPS Office can provide additional resources for Excel users.
