Excel countif: YOUR OBJECTIVE
You have a list of your company’s international sales. Analyzing the data in that table, you want to find out how many sales were made in individual countries. Ideally, you would like to type in a country’s name and have Excel count and then display the number of sales made in that country. Excel’s COUNTIF function is suitable for this purpose. It counts the number of times a certain value is found within a given cell range.
This learning module will show you how to use the excel COUNTIF function.
HOW DOES THE EXCEL COUNTIF FUNCTION WORK?
The excel COUNTIF function is quite similar to the SUMIF function (described in another learning object). In our current example, cell H2 will contain the search criterion, i.e., the name of the country for which you want to determine the number of sales. You are going to use the excel COUNTIF function to find out how many times the country name in H2 occurs in your sales list. Begin by clicking in cell H3. Then start the Function Wizard by clicking the button just to the left of the formula bar. In the Insert Function dialog box that opens, you will find the excel COUNTIF function in the Statistical category (shown below). Select that function and click OK.
After clicking OK, the Function Arguments dialog box will appear. As you can see, the excel COUNTIF function takes two arguments: Range and Criteria.
In our example the Range is the cell range in column E that contains all country names. Select the entire column by first clicking the uppermost cell in column E. Then, while still holding the mouse button depressed, drag your mouse pointer down to the last populated cell in column E. Alternatively, you can select the entire column by clicking in the uppermost cell and then, while simultaneously pressing [Ctrl]+[Shift], press the[Arrow Down] key. Now click in the Criteria input box, and then click in cell H2, where you will soon enter the name of the county for which you want to determine the number of occurrences in the list. After clicking OK, you can enter, for example, Brazil in cell H2.
In cell H3, the number of sales made in Brazil, i.e., the number of occurrences of the word Brazil in the given cell range, will appear.
You don’t have to enter a cell reference for the Criteria argument. You can, if you like, type it directly into the Criteria input box. If you enter plain text, Excel will automatically set quote signs around it. You can also enter a number or a comparison (e.g., <5) in the input box.
If you click on cell H3, you will see the complete function in the formula bar:
You can read this formula as follows: Count how often the value that is given in cell H2 occurs in the cell range E2:E2156.