Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.
This article looks at 4 advanced techniques for summing values including;
A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.
This tutorial looks at four techniques for separating text.
A common question from Excel analysts and enthusiasts on my courses is to count unique values in a list.
This post looks at using a formula to calculate this distinct count.
Consider the list below of a list of delegates attending our courses. A normal count on this range will tell us how many attendances there were. That’s good, but we want to know how many unique attendees there were.
At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.
The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.
Having formula errors on a spreadsheet can be bad news. They look ugly, they frighten the less Excel experienced among us and they stop our other formulas from working.
If you have formula errors on a spreadsheet it is normally best to stop it at its source. To either correct the error, or to hide it using formulas such as ISERROR and IFERROR.
However, if your spreadsheet is large, having these IFERROR functions in every cell to protect against error values will add more calculation time to your spreadsheet.
There is a function in Excel called AGGREGATE which allows us to perform various functions on a range whilst ignoring formula errors.
