Excel – Counting Functions
Ever sat at your desk holding a pen to the screen counting cells? I know I have in the past. Well…no need, Excel has a bunch of counting functions to help you out.
In this blog I am going to look at five counting functions:
The most basic of the counting functions. You can find this in the drop down list next to the AUTOSUM button.
Somewhat helpfully, it shows COUNT NUMBERS which is exactly what COUNT does. It only counts cells that contain numbers and ignores text cells. Alternatively, just type =COUNT( and select the cells you want to count. In the screenshot below you can see what COUNT returns depending on the contents of the selected cells.
This is the counterpart to COUNT. This will count cells that contain anything – text or numbers. If you want to count cells that only contain text then you have to use COUNTA otherwise you get a count of 0. Again, the screenshot below shows you the various results based on the contents of the cells
Does exactly what is says…counts blank cells. Can’t say any more than that really. Note however, that if a cell contains a space (typed by accident probably) it will ignore it as the cell is no longer “blank” as it contains a space character.
Slightly more complex than the previous three but very useful. The COUNTIF function counts how many times a specific value (numerical or text) appears in a range of cells. For example you might be organising an event and need to get people’s choice for main course at the evening dinner. Let’s say there is a choice between fish, beef, chicken and vegetarian. You need to know how many people have opted for each of the mains – this is where COUNTIF can help.
The syntax for COUNTIF is as follows:
=COUNTIF(range of cells to check, what to count)
If you are counting text values then the “what to count” bit must be in double speech marks i.e. “fish”.
If you want to count how many times a number appears then no need for any speech marks. However, if you are counting how many numbers are greater than, less than etc. , you need to put this in speech marks too (this often catches people out and is not really logical). For example I want to count numbers greater than 10, my formula would read:
A few examples below:
Whereas COUNTIF checks a single range of cells, COUNTIFS allows you check multiple ranges each with different criteria. In order to count, all criteria must be met in all of the selected ranges. You can create up to 127 different range/criteria combinations.
The syntax for COUNTIFS is as follows:
=COUNTIFS(range1 to check,range1 criteria, range2 to check, range2 criteria….)
Using our example of the menu choices, let’s extend our choices to include wine. So people can choose a main and a choice of red, white or rose. Our function will then have to count people who want fish and white wine, fish and rose or (perish the thought) fish and red wine, and then the same for each of the other mains.
So there you have a selection of counting functions. Never again will you need your biro to point to cells on screen whilst trying to scroll down at the same time!
Check out my other blogs on functions – date and text functions.