Excel Tip of the Week #35 - SUM, COUNT, COUNTA, COUNTBLANK

Hello everyone and welcome to another Tip of the Week.  This week we have a General User post in which we go back to basics and look at four of the simplest and most commonly used formulae that Excel has to offer.

What SUM does: SUM is a Math & Trig function.  It is used to return the total of any amount of cells added together.  It will use the results of any formulae already in the cells, and will work with both negative and positive numbers.

How to write a SUM function: SUM can be used in one of two ways, either with a series of discrete cells being selected or a range of cells; i.e.: 
Where the numbers in the brackets are the cells that are to be added together.  It's not usually best practice, but you can also enter numbers directly into the function, rather than referring to cells, e.g.:
                                    =SUM(2, 8, 5)
You can even use a mix of references and numbers to come to the final SUM:
                                    =SUM(2, A1, 8)
Because SUM can be used to add both cells and hard coded numbers, and will take into account the results of any calculations within the cells selected, it is a useful tool for totalling the product of a number of other calculations, creating checks in a spreadsheet, and quickly adding up a number of cells at once, by selecting a range.  It will also ignore any non- numerical cells when it is operating, so if you select a cell with writing in, it will simply not be included in the sum rather than returning an error.

Now, let’s move on to COUNT – a similarly simple formula, but one that has a number of subtle variants which can be useful in specific circumstances. 

What COUNT does: COUNT is a Statistical function. It is used to count (the clue is in the name!) how many of the cells selected contain numeric values. As with SUM, results of formulae in the selected cells are counted by the function – so if the result of one of these formulae is a numerical value, it will be counted.

How to write a COUNT function: Exactly the same way as SUM, by choosing discrete cells or a range:
                                    =COUNT(A1, B12, C18)
COUNT is a simple formula, with a simple output. It has its uses, although these can be limited. For the majority of day to day calculations you use Excel for, SUM will be much more commonly used. However, for data analysis purposes, COUNT will be of use, particularly when working with large amounts of data.

COUNT only works with numerical values, so if you want to count a different type of data you’ll need to use one of these other functions to achieve a similar result. COUNTA will count all non-blank cells in a selected range: conversely COUNTBLANK will count all blank cells. Let’s look at these together.

What COUNTA and COUNTBLANK do: COUNTA / COUNTBLANK are Statistical functions. COUNTA is used to count how many of the cells selected have any type of information in them. This includes error values, text, numbers and empty text (““ – no spaces). As with SUM and COUNT, if a cell containing a formula is selected as part of the range, COUNTA will use the result of this formula. 
COUNTBLANK unsurprisingly, counts how many cells are blank. This is any empty cell, or a cell containing empty text. (NB empty text would usually only come up as a result from a formula). It will not count errors.

How to write a COUNTA or COUNTBLANK formula: Exactly the same as COUNT:
                                    =COUNTA(B19, A6, D89)
                                    =COUNTBLANK(B19, A6, D89)
Depending on the type of data you are working with and the way you are using it – you might need to use one or all of these functions. 

Have a look at the attached spreadsheet for some examples of how each function will give you a different result on the same set of data. Try adding more data and/or different formulae to see how this effects the results of each formula.

Previous post 
TOTW index
Next post

Thanks to Emma Horton for her input on this Tip.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.