Learn The Secrets Of COUNT Functions In Excel

Thu 23rd June 2011

Once you've created calculations in Excel you might then have moved on to use Excel's own built in calculations in the form of functions. A popular Excel function is COUNT and this article summarises the different COUNT functions available and describes how each are used.

Once you've created calculations in Excel you might then have moved on to use Excel's own built in calculations in the form of functions. A popular Excel function is COUNT and this article summarises the different COUNT functions available and describes how each are used.

All versions of Excel have COUNT, COUNTA, COUNTBLANK and COUNTIF and Excel 2007/2010 also has COUNTIFS. We'll describe each of these functions in turn starting with COUNT. All these functions have a similar structure, an equals sign, then the function word, followed in brackets by the range of cells we want to count.

COUNT

The COUNT function counts how many cells in a specified range contain numbers, and looks something like =COUNT(RANGE) where RANGE indicates the specified cells. Suppose we have a list of sales figures for four shops in four separate cells D1 to D4. The figures are 10, 15, 5 and a blank cell. The fourth cell is blank because the sales figures for the fourth shop are missing. In cell D5 we type in =COUNT(D1:D4). The results in cell D5 show 3. This is because in our range of four cells, only three have numbers. If we now type "Missing" without the quotes into cell D4 we'll see that cell D5 still shows 3, as in our range of four cells, there are still only three containing numbers.

COUNTA

The COUNTA function counts how many cells in a specified range contain any value rather than just numbers, and looks something like =COUNTA(RANGE). To show this in action we'll first delete the contents of cell D4 in our four numbers example, so D4 is again a blank cell. Now in cell D5 we type =COUNTA(D1:D4). You'll see that cell D5 still shows 3 because in our four cells only three contain any values and one is blank. Now once again type "Missing" without the quotes into cell D4. This time we'll see that cell D5 shows 4 because now all four cells contain values, which in this example are numbers and text. Another way of describing it is to say that the COUNTA function counts the number of non blank cells in a specified range.

COUNTBLANK

Straightforward this one I think and it looks something like =COUNTBLANK(RANGE). Suppose you again delete the contents of cell D4 in our example, so it's empty. Then change the function in cell D5 to =COUNTBLANK(D1:D4). Of course you'll see that cell shows 1 because in the range of cells we specified there is one blank cell. So the COUNTBLANK function counts how many blank cells there are in a specified range.

COUNTIF

The COUNTIF function counts how many cells in a specified range meet a particular condition and it looks something like =COUNTIF(RANGE,CONDITION) where RANGE indicates our specified cells and CONDITION is the value we are looking for in these cells. The function will count how many cells in the range have this value. Suppose we have a list of cells from E1 to E10 with six containing "yes" and four containing "no". We can use COUNTIF to count how many cells in the range contain "yes", and another COUNTIF to count how many contain "no". So in cell E11 we type =COUNTIF(E1:E10,"yes") and see that cell E11 shows 6. In cell E12 we can type =COUNTIF(E1:E10,"no") and of course the cell will show 4.

When you use the condition part of a COUNTIF function you can put the condition value in a separate cell, and then use the cell's reference in the function. You could then easily change the value in this separate cell to change the condition in the COUNTIF function if you wanted to. Let's look at an example, where the condition is >3. A condition can include arithmetic operators such as more than or less than symbols as well as numbers. Suppose you have ten cells G1 to G10 containing the numbers 1 to 10. In cell H1 we type in ">3" (without the quotes). Then in cell G11 we type =COUNTIF(G1:G10,H1). Cell G11 will then show that 7 cells in the range have values greater than 3. Try changing the value of cell H1 to <7 and cell G11 will show that 6 cells have values less than 7.

COUNTIFS

If you use Excel 2007/2010 then you also have the very powerful COUNTIFS function available. The COUNTIFS function counts how many cells in a specified range meet more than one condition. And you can use up to 127! It looks something like =COUNTIFS(RANGE,CONDITION1, RANGE,CONDITION2, etc). You can choose different condition ranges for different conditions, but to start with you might like to stick to the one data range for every condition. Let's look at an example using the ten cells G1 to G10 containing the numbers 1 to 10. We want to apply two conditions and place these in separate cells, so in cell H1 we add >3 and in cell H2 we add <7. We're going to use COUNTIFS to count the number of cells in our range with values more than 3 and less than 7. So in cell G11 we type =COUNTIFS(G1:G10,H1,G1:G10,H2) and find that cell G11 shows the answer as 3, and there are indeed 3 cells in our range with values more than 3 and less than 7.

Interested in exploring Excel functions further? There are many more functions to discover in Excel and one really effective way to do this is to attend a training course. The best ones are hands on and help you explore Excel in more depth. This can really boost your Excel skills.

Author is a freelance copywriter. For more information on microsoft excel courses london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1747-learn-secrets-count-functions-in-excel.html