Excel is a truly amazing app that’s helped countless people analyse and manipulate large sets of data. In my opinion, it is a great tool that can find some sort of utilization in just about any field (except maybe oil rigs). A short while back we did a post on five Excel functions that can be exceptionally useful in making mundane, boring, repetitive work considerably easier. Many of us, specially those doing managerial or administrative tasks on a daily basis grow quite familiar with many of the functions that Excel supports and while we may know how to use one, where to use it and how a function can make life easier isn’t always so obvious. Today I’m detailing three uses of the very simple ‘Count’ function in Excel.
By the ‘Count’ function, I mean all variable forms of said function which includes the CountBlank function and the CountIf function.
This function simply counts cells in the range you specify and you might be tempted to overlook it since Excel will give you the number of cells in a range if you select them. One great use for the Count function is for quantifying string values. This can be something as simple as counting the number of items listed in a column of stationery items issued to a particular department. The function is best used when the data in cells is all different.
Syntax: =CountIf(A1:A2, “Absent”)
This is a conditional argument applied to the Count function. Basically it’s asking the application to count all cells that contain a particular value that you define yourself. This can be anything from a number, a word, or an argument like greater than or less than a number. The function can be used to extract quantified information from a workbook that contains a diverse dataset. Imagine having to extract how many employees in a department were late on a particular day when all you have to work with is an unsorted sheet full of names and arrival times. The CountIf function can easily find the number of times a particular entry that is of interest to you occurs in a dataset. It can be applied to both numeric and alphabetical values meaning if you wanted to find out how many people in the office are named ‘Charlie’, this is the function to use. You can also use it with wildcard entries so the application is limited only by your imagination (and the kind of work you have to do). In an emergency, you can even use this function to find out how many employees have a particular blood group.
Trying to look for missing information and unsure just how incomplete the data on hand is? Give the CountBlank function a try. You can use it to see how many people haven’t provided essential information, if you have a list of vendors and want to see if any information about any of them is missing, or for finding out if your dataset contains bogus entries.
Use it well: The Count function, and all its variables seem to do very little when you think of them by themselves. Combine them with a few other simple features like sorting and highlighting and you will be surprised at the new possibilities that open up to you. Combine it with other Excel functions and you’re practically an Excel ninja.