Excel – Counting Functions

Abacus Counting

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:

  • COUNT
  • COUNTA
  • COUNTBLANK
  • COUNTIF
  • COUNTIFS

COUNT

The most basic of the counting functions. You can find this in the drop down list next to the AUTOSUM button.

Basic COUNT function

Basic COUNT function

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.

Applying the COUNT function

Applying the COUNT function

COUNTA

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

Applying the COUNTA function

Applying the COUNTA function

COUNTBLANK

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.

Using COUNTBLANK - watch out for spaces!

Using COUNTBLANK – watch out for spaces!

COUNTIF

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:

=COUNTIF (A1:A20,“>10”)

A few examples below:

Using COUNTIF to work out menu requests

Using COUNTIF to work out menu requests

COUNTIFS

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.

Using COUNTIFS to work out main and wine combos

Using COUNTIFS to work out main and wine combos

A closer look at the COUNTIFS function

A closer look at the COUNTIFS function

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.

 

 

Advertisements

Posted on June 3, 2014, in Functions & Formulas and tagged , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: