Here is a Sample File for you to download. Therefore I would do this trick without GROUPing columns but bear in mind of the F9 key. Moreover, it may not be easy to group columns with real life data in a seamless way without adding extra columns. The steps below will walk through the process. The OFFSET function eliminates the need for entering the desired range manually and the need for a complex formula. Nevertheless, doing the GROUPing is kind of tedious. We can use the OFFSET function to sum a range of rows and columns from a reference cell. With this workaround, we save the key stroke of F9 after hiding / unhiding. Use fill down and the four totals will appear. This will count the As, as d1 has an A in it. Interestingly, hiding/unhiding columns by using the +/- button does trigger re-calculation. So, for example, if you were trying to count the appearances of your letters in cells A1:A10, you could list your four values (A,B,C,D) in a column (say, d1,d2,d3,d4.) In cell e1 put the formula countif (a1:a10,d1).
Note: as we have inserted three columns, the formula in J4 (which was previously in G4) is now extended to cover column A:H. We should be able to get the above layout. Now we should see the +/- button on top of column labelĤ) Repeat Grouping to columns D:E, and G:H (tips: Select the columns and then press F4, which is to repeat previous action) Please follow the steps below:ġ) 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)ģ) Data tab –> Outline Group –> Group ( shortcut tip: Alt+Shift+Right arrow) (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. We need to give it a little push, say pressing F9, after hiding/unhiding columns. To see what the SUM function looks like, click on the. 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. By default the AutoSum tool creates a SUM function, which adds together the values in the selected cells. Now we may hide column C:D to see the result... Put it in other words, SUM visible columns only. It simply instruct Excel to sum the range of A4:F4 where corresponding cells in A1:F1 is greater than zero (“>0”). In A1, input the formula: =CELL("width",A1) 'copy across to F1Īs mentioned in previous post, the function CELL(“width”,A1) returns 0 (zero) when the column is hidden. What we need is a helper row, say Row 1 in our example, and SUMIF function. If we need to SUM visible columns only, we will need a twist. However, there is no similar function for visible columns only. We know that SUBTOTAL allows us to perform some basics functions like SUM, COUNT, AVERAGE, etc.