The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
Tag Archives: SUM
Conditionally Selecting a Column in Excel
The IF function can return a range
When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula.
Running Total in a Formatted Table
Using the SUM function
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Status Bar Calculations in Excel
Quick and easy calculations
Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
Summing Coloured Cells in Excel
A SUBTOTAL trick worth learning
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
Excel 3D SUMIF
You can achieve it
In a previous blog post I explained how to do a 3D SUM calculation in Excel that SUMs through sheets. You can view it here. I have seen a few posts in forums recently about performing a 3D SUMIF calculation. Whilst the SUM function can work with 3D ranges, the SUMIF function can’t.
Handling Text in Calculation Cells in Excel
If you need to do mathematical calculations on cells that may contain numbers or text, then you can use the IFERROR function to ignore the text.
YTD Percentages in Excel
Getting percentages right
Calculating YTD percentages in Excel isn’t always straightforward, but in some cases you can use Excel’s most flexible function to achieve the result you need.
FORMULATEXT Function in Excel 2013
New function - great for training
The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.
Quickly and easily hiding and unhiding rows and columns in Excel
Excel's Grouping technique
Hiding rows and columns is a common process in spreadsheets. Excel’s grouping feature can make the process easier and make it more apparent when rows or columns have been hidden.
Using wildcard characters in Excel functions
Handling missing characters
Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria.
Using Excel’s IF function differently
A Shorter IF
The IF function is typically used to display one result or text when a condition is met and another if it is not met. There is another technique using the IF function that can reduce formula size in certain circumstances.
SUBTOTAL Function- better than SUM
Summing visible cells only
The SUM function is the function of choice to add up values. But when it comes to subtotalling, SUM leaves a lot to be desired. There is a specific function for subtotalling called, funnily enough, SUBTOTAL.
3D SUM – SUM through spreadsheets
No glasses required
The SUM function can perform 3D sums. The method is useful in certain circumstances and is both efficient and flexible. See the example file link at the bottom of this blog post.
Welcome to the Excel Yourself Blog
Do you know everything about Excel?
I’ve been using it for over 20 years! I’ve learned a lot in those years, but I’m still learning new things and this blog aims to share my knowledge. Of course new versions are always adding new features, so it can be hard to keep up.
I’m a CPA and this blog will discuss Excel topics that are directly applicable to Accountants and Finance staff. The content will vary from simple to complex; from functions to formatting; from Excel 2003 to Excel 2010.