Welcome back to the Excel Tip of the Week. In this week's General User post, we are looking back to TOTW #35 and revisiting some of the most commonly used formulae in Excel. First up is SUM – a formula I personally use just about every day. What it does: SUM 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 one: 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.: =SUM(A1:A45) =SUM(A1, A2, A3) You can also enter numbers directly into the function, or use a mix of references and numbers to come to the final SUM: =SUM(2, A1, 8) SUM will take into account the results of any calculations within the cells selected, treating them as if the result of the formula in the cell was a hard-coded number. 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. SUM is useful for more than just totals. You can use it together with other formulae to create checks in more complex spreadsheets or combine it with logic in an IF formula. COUNT is a similarly simple formula, but one that has a number of subtle variants which can be useful in specific circumstances. What it does: COUNT is used to count how many of the specified cells have numerical 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 one: Exactly the same way as SUM, by choosing discrete cells or a range: =COUNT(A1:A10) =COUNT(A1, B12, C18) COUNT is a simple formula, with a simple output. COUNT is useful for analysis when working with large amounts of data. COUNT only works with numerical values, ignoring anything that it doesn't recognise as a number (including numbers stored as text), so if you want to achieve a similar result with a different type of data you’ll need to use either COUNTA or COUNTBLANK. It’s worth noting that COUNT does recognise dates as numbers, so these will be counted using this function. COUNTA and COUNTBLANK are useful if you aren't only working with numbers. What they do: COUNTA is used to count how many of the cells selected have any type of information in them (“non-blanks”). This includes error values and empty text so check for things like this if you find your results are not what you were expecting. 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. (N.B. empty text would usually only come up as a result from a formula). It will not count errors. How to write one: Exactly the same as COUNT: =COUNTA(B5:B90) =COUNTA(B19, A6, D89) =COUNTBLANK(B5:B90) =COUNTBLANK(B19, A6, D89) The COUNT family of formulae are most commonly used when working with particularly large data sets. They are a quick way to find out how many items are in a particular section and identify if you have any gaps in a range. You can also combine with more complex formulae and logic to tailor the data analysis the way you want it. Have a look at the attached file for some examples of how each function can be used and the types of outputs you will see. Please note that although this is a revisited topic, the examples are all brand new! There are two sheets on this file, to show different types of data and how the functions can be applied in a meaningful way. Thanks to Emma Horton for her input on this Tip. Previous post - Revisiting VLOOKUP TOTW index Next post - AutoSum 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.
Hi Gary - rather unhelpfully, I think the answer is probably: "it depends..."
The two formulae will not necessarily return the same result, due to the way SUM() handles text values. If one of the cells contained text, then =A1+D5+J17 would return a #VALUE! error whereas the SUM() formula would just ignore the text cell and add up the other two cells.
If the text was actually a number entered as text then =A1+D5+J17 would 'coerce' the text number to be treated as a 'normal' number and it would be included in the addition. SUM() would still ignore it. Subtracting J17 makes a difference, as the subtract operator in the SUM() function will coerce a text number to a number or cause a #VALUE! error for text text.
If you know that all the cells will contain normal numbers then I would generally go for the =A1+D5-J17 option on the basis that it is slightly simpler than using the SUM() function. It might also be that, with enough calculations, SUM() might take slightly longer to calculate.
There are a few Excel Community articles on SUM() and coercion:
Suppose I want to add up the values in cells A1, D5, and J17, and no other cells. Which is better:
= A1 + D5 + J17
= SUM(A1, D5, J17)
Seems like a trivial point, but I always find myself pausing for a few seconds when these situations come up, and I then spend the next few minutes wondering if I did it "right".
And what if you want to subtract J17. Which is better then:
= A1 + D5 - J17
= SUM(A1, D5, -J17)