March 19, 2014

COUNTIF Function

The COUNTIF function in Microsoft Excel is a simple yet powerful function to analyze our spreadsheet data based on a specific condition. You remember how we used the COUNTA function to count the number of students. COUNTA counts all the values.
Now what if we wish to know how many managers work in our company or who draws a certain amount of salary? The COUNTIF function can help us answer such specific queries. The function takes the form:
The range is the area of cells which has the data and criterion is the condition based on which we wish to count. For example:
Our data of designations is in the cells range A1:A12 and we wish to find out how many of the designated employees are managers. You can also use wildcard characters like “M*” which would mean I wish to find all designations or names starting with M. In a cell range that contained names like “Maria”, “Mathur” and “Mary”, the function would return a count of 3.
You could also use the function to find out all the employees having a salary of greater than or equal to 15000 and the function would be written as:
assuming that the data of salaries is in range A2:A12.
Now selecting ranges while working with large amounts of data can be cumbersome. So what do you do? MS Excel has a fine solution. Select the range and give it a name like we have shown in the training video where the range of cells having the designations is named as “designation”. So now instead of selecting the range you start to type ‘de…’ the name appears in the drop-down list from which you can select it and use it quickly. Also you can sometimes check for the correctness of the procedure by selecting the data from the cells range and observing the count on the status bar. This point has also been demonstrated in the video.
Watch the video:

One thought on “COUNTIF Function

Comments are closed.