Don’t you hate it when you know there’s something wrong but you can’t exactly put your finger on what is happening?

Try this, filter a list in Excel and write an =sum function at the foot of the list to sum the visible data. So far so good? Well, try checking that sum manually – do you still feel confident? Worse still, if you’re using Excel, try to filter the numbers in the column containing the Sum formula and watch as Excel chews up your formula – yikes!

You see, =SUM just doesn’t work on filtered lists. Instead, you have to use SUBTOTAL. Of course, there’s a simpler way. Use the AutoSum button on the Excel toolbar to create your formula and it does the sensible thing and writes a SUBTOTAL function for you. Now, when you filter the data it sums only visible values and it never gets swallowed up.