Recently I got a workbook with this formula:
I’m sure you’ve seen a few formulas like this. I know I have. I may have even made one or two. There is a better way, however. Usually a formula like this means there are several lists in the column with subtotals and this formula returns a grand total. The problem is when you want to add a new list or item and include it in the total. It’s not hard to add a +F62 to the above formula, but if you forget to do it, it’s not always obvious that the formula is wrong.
I changed the above using the SUBTOTAL function (with 9 as the first argument because I’m summing). One really nice feature of SUBTOTAL is that it ignores any cells that have a SUBTOTAL function in them. I can SUBTOTAL the whole range, and as long as I’ve used SUBTOTALs within the range, I don’t have to worry about double counting. If I add a new item in the middle, the SUBTOTAL range will adjust to accommodate.
Here’s an example of the old way:
Here’s the same example using SUBTOTAL: