[Solved] Do not sum hidden cells in columns

[restorwoodwindows]

I've been searching for the answer and can't seem to find a decent answer so here goes.

I want to calculate the SUM of all the VISIBLE cells across my columns. There are a number of columns that are hidden and I don't need those cells included in the total.

So the function is horizontal across the spreadsheet and not vertical.

I appreciate the help!

Edit: Changed subject, was Do Not Calculate Hidden Cells in Column Totals  Make your post understandable by others  The sum of cells across columns is a total for that row, not a "column total".  -- MrProgrammer, forum moderator

[Zizi64]

The function SUBTOTAL(109;range) works for rows when some rows are hidden.
But it seems not work when I use it for columns.

(LibreOffice 6.1.6)

[MrProgrammer]

I want to calculate the SUM of all the VISIBLE cells across my columns. There are a number of columns that are hidden and I don't need those cells included in the total.

You cannot hide cells. You can hide columns, and you can hide rows. In this situation you seem to be hiding columns.

The basic problem is that formula calculations are based on cell values, not on row/column visibility or other formatting. However, I know two ways to accomplish this, though neither is entirely satisfactory to me as the process is not fully automatic. But that is to be expected when one wants to do something that Calc doesn't support.

You can use a row containing cells in its columns to indicate whether you want the column included in the total. When you change the values in row 2 of my attachment this affects whether the respective column(s) are considered in the total. But you must set the values in row 2 to FALSE before you hide columns and set them to TRUE after you show them.

You can use the =CELL("WIDTH") formula to calculate whether to include the column as you hide or show it. But the CELL() function is not recalculated automatically with the hide/show operations. You must press ⇪⌘F9 (Shift+Command+F9) on a Mac or Ctrl+Shift+F9 on other platforms to force the recalculation of the column width.

Edit: 2022-06-08 To get an automatic recalculation use =CELL("WIDTH")+T(RAND())

[Tutorial] The SUMPRODUCT function

For either method, if you have specific sets of columns which you often want to show/hide, you can use recorded macros to perform those operations, and they can then either change the values in row 2 or trigger the forced recalculation of cell width. For example, if you have one set of columns for Team A and another set for Team B, you can record macros HideA, ShowA, HideB, and ShowB. If you have several different sets, you may want to record a macro for ShowAll. Or you could have HideAll and then select various sets for display. Using Tools → Customize you can put buttons for the macros on your own toolbar.
[Tutorial] Favorite Recorded Calc Macros

The function SUBTOTAL(109;range) works for rows when some rows are hidden.

As I understand it, SUBTOTAL() calculations are affected by Data → Filter, but not by Format → Row/Column → Hide/Show. Filters only apply to rows and so SUBTOTAL is not useful in this situation.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[eeigor]

See AGGREGATE() with 2nd arg set to 5

[robleyd]

See AGGREGATE() with 2nd arg set to 5

Unfortunately that function is not available in AOO, only in LibreOffice.