Excel function learning: Let’s talk about the classic usage of COUNTIF function

In the previous article " Excel Function Learning: A Brief Analysis of the Usage of Sumif() and Countif() " we learned about the application of sumif and countif functions. This article will take you to understand the COUNTIF function. , introduce the classic application skills of COUNTIF function, I hope it will be helpful to everyone!

#The COUNTIF function is a relatively simple function, but it is also very practical when used properly. The function of the countif function is to calculate the number, indicating the number of cells in the calculation area that meet the given conditions.

The syntax of the countif function is: COUNTIF(range,criteria) , where the Range parameter: is the cell range in which the number of cells that meet the conditions needs to be calculated, and the Criteria parameter: is determined The criteria for which cells will be counted, which can be a number, expression, or text.

The picture below is a computer accessories sales table. In this sharing, we need to complete the following knowledge points.

First, count the number of "quantity" greater than 30 and the number of "unit price" less than 100 in the data source above.

first look at the number of "quantity" greater than 30, and apply the grammar of the Countif function: countif (region, condition), and draw the formula: = Countif (D6: D35, "& GT ;30") , the result is: 8.

Note: > = in the formula must be " " , and refer to the unit There is no need for " " .

You can also use array formulas for this question: =COUNT(IF(D6:D35>30,1)) , press [Ctrl] [Shift] [Enter] Three keys to complete the input of the array.

You can also use this formula: =COUNTIF(D6:D35,">"&D7) . The symbol " & " is the text paste symbol, followed by the cell address, which means to connect the content of cell D7. You can check that the content of cell D7 in the worksheet is 30. If there is no function after greater than , there is no comparison target, so &D7 is used.

There is a way to view part of the formula, which is to press the F9 key, commonly known as "smear". The F9 key is of great help to us in learning functions and formulas, helping us understand the formulas.

In this formula, if you select the following ">"&D7 in the formula editing bar and press the F9 key, you can see that it changes to ">30", just like the first formula designed That is: =COUNTIF(D6:D35,">30")

Of course, to solve a problem, there may be more than one formula designed. As long as everyone is flexible and flexible based on their own understanding, Just use it and the answer will be the same.

Use the same method to get the number of items whose "unit price" is less than 100. The formula is: =COUNTIF(D6:D35,", and the correct answer is: 30 .

Second, count the number of "river" characters in the "sales department" in the data source above, and whether there is a keyboard in the "product" column.

To find out the answer, first of all, you need to understand the rules for using wildcard characters in the COUNTIF function. The COUNTIF function supports wildcard characters, and wildcard characters can be quoted in the COUNTIF function. The wildcard symbol ? represents a single character, and the * symbol represents multiple characters.

Therefore, to count the number of words containing the word "river" in "Sales Department", the formula is: =COUNTIF(A6:A35,"*河") , and the correct answer is: 12.

Another way to write the formula: =COUNTIF(A6:A35,"*河*") can also get the answer.

In fact, the wildcard * sign is similar to the find function, so you can also design the formula like this: =COUNT(FIND("河",A6:A35)) , and then press [Ctrl] [Shift] [Enter] Three keys to complete the input of the array.

If there is a keyboard in the "Product" column, you can design the formula like this: =IF(COUNTIF(B6:B35,"Keyboard"),"Yes","No") .

Third, list the number of times "Tianhe", "Huangpu River", "Huangpu", "Yuexiu", "Liwan" and "Chaoxiu" appear in the sales department in one row.

To count the occurrences of multiple cells at once, you can use array formulas. The method is: First select the L19:L24 cell range, then enter the formula =COUNTIF(A6:A35,K19:K24) in the edit bar, and then press [Ctrl] [Shift] [ Enter】Three keys to complete the input of the array. The answer is: 9, 3, 4, 7, 6, 1 times.

In addition, for this question, you can also use the ordinary formula: =COUNTIF($A$6:$A$35,K19) , then pull down and copy the formula.

Fourth, count the total number of occurrences of the date under the "Sales Date" column.

Calculating the total number of occurrences of dates in the data source also requires the use of array formulas. This formula is also a classic example on the Internet. The formula is: =SUM(1/COUNTIF(C6:C35,C6:C35)) , and then three keys to end. The result is: 30.

Formula analysis: In the formula, the result of COUNTIF(C6:C35,C6:C35) is 1. You can blackout this part in the above formula to get the result: 1. It is to count the number of times the content of each cell C6:C35 appears.

In the formula 1/Countif (C6: C35, C6: C35) Partially discredit, execute the formula, get the array of {1; 1; 1; 1; 1; 1; 1; 1; 1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1; 1} , there are 30 1's in total. 1/ is the percentage of 1 that each value accounts for, to add up. After viewing the formula results, you can press the Esc key to return to the formula. Finally, use the SUM function to total.

This question can also be designed with the following two formulas, both of which can achieve the results.

First: =SUM(--(MATCH(C6:C35,C6:C35,)=ROW(C6:C35)-5)) , Three keys to end.

Second, design a common formula: =COUNT(1/FREQUENCY(C6:C35,C6:C35)) .

Some netizens mentioned that you can also use the formula for this question: =COUNTIF(C6:C35,"0"). There is something wrong with this formula. It only counts the data in the C6:C35 area. In this question, although the answer is the same, it is because the dates are not repeated. If there are two identical dates in the source data, the result will be wrong. You can do a small experiment by changing the date in the source data.

Fifth, how many unique values are there in the "Product" column?

This question can be understood as extracting unique values. The simplest method is Use advanced filtering, steps: click the menu "Data" - "Filter" - "Advanced Filtering", select under "Method": Copy the filtering results to other locations, the list area is: $B$6:$B $35, copy to: K32, check "Select non-duplicate records".

Description: The disadvantage of advanced filtering is that after the source data in the worksheet is updated, the filtered results will not be updated.

This question can also be solved using formulas. The three formulas designed below can all achieve the final result.

Formula 1: =INDEX($B$6:$B$35,MATCH(,COUNTIF($L$31:L31,$B$6:$B$35),) ) , then triple-click to end formula input and pull down.

Formula 2: =INDEX($B$6:$B$35,SMALL(IF(MATCH($B$6:$B$35,$B$6:$B$35 ,)=ROW($B$6:$B$35)-5,ROW($B$6:$B$35)-5,1000),ROW(A1))) , then press three keys to end the formula input and pull down .

Formula 3: =LOOKUP(1,0/(NOT(COUNTIF($K$31:K31,$B$6:$B$35))),$B$6 :$B$35) , then scroll down and copy the formula until the #N/A error value appears. The answer to this question is: hard drive, monitor, mouse.

Related learning recommendations: excel tutorial

Excel function learning: Let’s talk about the classic usage of COUNTIF function

Related articles