Excel Tip: Exclude hidden rows from total

Although it is worth noting the dangers of hiding rows (see this earlier post for a particularly embarrassing example), they can be useful. If you do use them, you may want any totals on the sheet to exclude the hidden rows, so that the data presented is consistent (i.e. so the total is the total of the numbers you can see).

Fortunately, Excel provides a useful tool for this.

Normally we use the SUM command to add the values in a range, so if we wish to add up column A, rows 1 to 100, then we would use:

=SUM(A1:A100)

This is great, unless we hide row 30 for some very good reason!

If someone was to manually check your sum, they would find that the total wasn't correct (given the numbers they could see that it was supposed to be adding up). This is because the SUM command will include the hidden row.

We can, however, use a different command to do this that will exclude the hidden row. This command is SUBTOTAL.

=SUBTOTAL(109,A1:A100)

will do the same as the SUM command above, but ignore the hidden rows in the total.

I know you're thinking "Where did that 109 come from?" (like many a Battle of Britain Spitfire pilot!)

Well in this case it's not the Luftwaffe, but part of the workings of the SUBTOTAL function.

This first argument can be one of 22 numbers in the ranges, 1 to 11 and 101 to 111.

The numbers 1 to 11 cause the function to mimic the following Excel functions:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

So,

=SUBTOTAL(9,A1:A100)

would work almost identically to our SUM function, except for the following two subtle differences:

1. It will exclude rows hidden by Autofilter
2. It will also exclude any other SUBTOTALs in the range

This will not, however, exclude rows hidden using the Hide command. That's where the numbers 101 to 111 come in. These work exactly the same as numbers 1 to 11, but this time also exclude hidden rows using the Hide command.

Hence our formula above:

=SUBTOTAL(109,A1:A100)

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".