A trick to SUM visible columns only (without VBA)

Excel Tips - SUM visible columns

We know that SUBTOTAL allows us to perform some basics functions like SUM, COUNT, AVERAGE, etc.  that apply to visible rows only.  However, there is no similar function for visible columns only.

Excel Tips - SUM visible columns 1If we need to SUM visible columns only, we will need a twist.

The twist is not difficult at all.  What we need is a helper row, say Row 1 in our example, and SUMIF function.

In A1, input the formula:

=CELL("width",A1) 'copy across to F1

As mentioned in previous post, the function CELL(“width”,A1) returns 0 (zero) when the column is hidden.

In G4, instead of a simple SUM, let’s do a SUMIF:


It simply instruct Excel to sum the range of A4:F4 where corresponding cells in A1:F1 is greater than zero (“>0”).  Put it in other words, SUM visible columns only.  YEAH!Excel Tips - SUM visible columns 2

Now we may hide column C:D to see the result…

Excel Tips - SUM visible columns 3

Wait!  It is still 21.  Nothing change.

Unfortunately, this is the limitation.  Although CELL is a volatile function (meaning it will get recalculated whenever there is a change in the worksheet), a simple HIDE/UNHIDE COLUMN action does not trigger recalculation.  We need to give it a little push, say pressing F9, after hiding/unhiding columns.

Excel Tips - SUM visible columns 4

(note: we may hide ROW 1 for better visual effect)

To further workaround it, we may GROUP columns and use the +/- button on the top to hide or unhide columns.

Please follow the steps below:

1) Insert 3 empty columns (C, F, I)  (tips: set column width to 2 which is more or less the width of the +/- button displayed on top)

2) Select columns A:B

Excel Tips - SUM visible columns 6

3) Data tab –> Outline Group –> Group (shortcut tip: Alt+Shift+Right arrow)

Excel Tips - SUM visible columns 7

Now we should see the +/- button on top of column label

4) Repeat Grouping to columns D:E, and G:H (tips: Select the columns and then press F4, which is to repeat previous action)

Excel Tips - SUM visible columns 8

We should be able to get the above layout.  Note: as we have inserted three columns, the formula in J4 (which was previously in G4) is now extended to cover column A:H.

Excel Tips - SUM visible columns 5

Interestingly, hiding/unhiding columns by using the +/- button does trigger re-calculation.

Excel Tips - SUM visible columns 9

With this workaround, we save the key stroke of F9 after hiding / unhiding.

Nevertheless, doing the GROUPing is kind of tedious.   Moreover, it may not be easy to group columns with real life data in a seamless way without adding extra columns.

Therefore I would do this trick without GROUPing columns; but bear in mind of the F9 key.🙂

Here is a Sample File for you to download.

What do you think about the trick? Please share with us by leaving comment.

This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s