How to Sum Only Visible Columns in Excel

In Excel, you can hide or unhide columns by right-clicking on the column header and selecting Hide or Unhide from the context menu. This can be useful for hiding irrelevant or sensitive data, or for simplifying the view of a large spreadsheet.

However, hiding columns does not affect the calculation of formulas that refer to those columns. For example, if you have a formula like =SUM(A1:C1) and you hide column B, the formula will still return the sum of A1, B1, and C1.

If you want to find the sum of the columns except for the hidden columns, you need to use a different approach. There are several ways to do this, depending on your situation and preference. In this article, we will explore some of the possible methods and provide examples.

Method 1: Use the SUBTOTAL function with the SUMIF function

One way to find the sum of the columns except for the hidden columns is to use the SUBTOTAL function with the SUMIF function. The SUBTOTAL function can perform various calculations on a range of cells, and it has an option to ignore hidden rows or columns. The SUMIF function can sum the cells that meet a certain condition.

The basic syntax of the SUBTOTAL function is:

=SUBTOTAL(function_num,ref1,[ref2],...)

The function_num argument is a number that specifies which function to use. For example, 9 means SUM, 1 means AVERAGE, etc. The ref1, ref2, etc. arguments are the ranges of cells to perform the calculation on.

The basic syntax of the SUMIF function is:

=SUMIF(range,criteria,[sum_range])

The range argument is the range of cells to evaluate the criteria. The criteria argument is the condition that determines which cells to sum. The sum_range argument is the range of cells to sum. If omitted, the range argument is used as the sum_range.

To use the SUBTOTAL function with the SUMIF function, we need to do the following steps:

• Add an extra row above the data and enter the formula =SUBTOTAL(103,A2) in the first cell. This formula will return 1 if column A is visible and 0 if hidden. The 103 argument means to count only visible cells.
• Drag the formula to fill the rest of the cells in the extra row. This will create a row of 1s and 0s that indicate the visibility of each column.
• In the cell where you want to display the sum of the columns except for the hidden columns, enter the formula =SUMIF($A$1:$C$1,">0",A2:C2). This formula will sum the cells in row 2 that correspond to the columns with 1 in row 1. The $ signs are used to fix the references to row 1. The “>0” criteria means to sum only the cells with a positive value in row 1.
• Drag the formula to fill the rest of the cells in the column where you want to display the sum. This will create a column of sums that exclude the hidden columns.

Here is an example of how this method works:

In this example, column C is hidden, so the SUM column shows the sum of column A and column B only.

Method 2: Use the CELL function with the SUM function

Another way to find the sum of the columns except for the hidden columns is to use the CELL function with the SUM function. The CELL function can return information about a cell, such as its address, value, format, etc. The SUM function can sum a range of cells or an array of values.

The basic syntax of the CELL function is:

=CELL(info_type,[reference])

The info_type argument is a text value that specifies what type of information to return. For example, “address” means the cell address, “value” means the cell value, “width” means the column width, etc. The reference argument is the cell or range of cells to get the information from. If omitted, the information is based on the last cell that was changed.

The basic syntax of the SUM function is:

=SUM(number1,[number2],...)

The number1, number2, etc. arguments are the numbers or ranges of numbers to sum. They can be constants, cell references, formulas, or arrays.

To use the CELL function with the SUM function, we need to do the following steps:

• In the cell where you want to display the sum of the columns except for the hidden columns, enter the formula =SUM(IF(CELL("width",OFFSET(A2:C2,,SEQUENCE(,COLUMNS(A2:C2),0))),A2:C2)). This formula will sum the cells in row 2 that correspond to the columns with a positive width. The width of a hidden column is zero, so it will be ignored by the formula.
• Press Ctrl + Shift + Enter to enter the formula as an array formula. This will make the formula evaluate each element of the array and return a single result. You will see curly braces {} around the formula in the formula bar.
• Drag the formula to fill the rest of the cells in the column where you want to display the sum. This will create a column of sums that exclude the hidden columns.

Here is an explanation of how this formula works:

• The OFFSET(A2:C2,,SEQUENCE(,COLUMNS(A2:C2),0)) part creates an array of references to the cells in row 2, from column A to column C. The OFFSET function returns a reference to a range that is offset from a given reference by a certain number of rows and columns. The SEQUENCE function returns an array of sequential numbers. The COLUMNS function returns the number of columns in a range. In this case, the OFFSET function returns an array of references like {A2,B2,C2}.
• The CELL("width",OFFSET(A2:C2,,SEQUENCE(,COLUMNS(A2:C2),0))) part returns an array of the widths of the columns in row 2, from column A to column C. The CELL function returns the information specified by the info_type argument for each cell in the reference argument. In this case, the CELL function returns an array of widths like {8.43,0,8.43}, assuming that column B is hidden and has a width of zero.
• The IF(CELL("width",OFFSET(A2:C2,,SEQUENCE(,COLUMNS(A2:C2),0))),A2:C2) part returns an array of the values in row 2 that correspond to the columns with a positive width. The IF function returns one value if a condition is true and another value if false. In this case, the IF function returns an array of values like {10,0,30}, assuming that the values in row 2 are 10, 20, and 30.
• The SUM(IF(CELL("width",OFFSET(A2:C2,,SEQUENCE(,COLUMNS(A2:C2),0))),A2:C2)) part returns the sum of the array of values that exclude the hidden columns. The SUM function returns the sum of the numbers or ranges of numbers in the arguments. In this case, the SUM function returns 40, which is the sum of 10 and 30.

Here is an example of how this method works:

In this example, column B is hidden, so the SUM column shows the sum of column A and column C only.

Explanation

In this section, we will provide a more detailed explanation of the concepts and formulas used in the previous methods.

Hiding and unhiding columns in Excel

Hiding and unhiding columns in Excel is a simple way to change the appearance of a spreadsheet without deleting any data. You can hide or unhide columns by following these steps:

• Select the column or columns that you want to hide or unhide. You can select multiple columns by holding the Ctrl key and clicking on the column headers, or by dragging the mouse over the column headers.
• Right-click on the selected column or columns and choose Hide or Unhide from the context menu. Alternatively, you can go to the Home tab, click on the Format icon in the Cells group, and choose Hide & Unhide > Hide Columns or Unhide Columns from the drop-down menu.
• The selected column or columns will be hidden or unhidden, and the column headers will adjust accordingly. You can tell if a column is hidden by looking for a double line between the adjacent column headers, such as A | | C.

The SUBTOTAL function in Excel

The SUBTOTAL function in Excel is a versatile function that can perform various calculations on a range of cells, such as sum, count, average, min, max, etc. It has an option to ignore hidden rows or columns, which makes it useful for filtering and subtotaling data.

The syntax of the SUBTOTAL function is:

=SUBTOTAL(function_num,ref1,[ref2],...)

The function_num argument is a number that specifies which function to use. There are two sets of numbers that correspond to different functions: one set that includes hidden values, and another set that ignores hidden values. The table below shows the available functions and their corresponding numbers:

The ref1, ref2, etc. arguments are the ranges of cells to perform the calculation on. You can enter up to 254 references, separated by commas.

For example, the formula =SUBTOTAL(9,A1:A10) will return the sum of the values in A1:A10, including any hidden rows. The formula =SUBTOTAL(109,A1:A10) will return the sum of the values in A1:A10, excluding any hidden rows.

The SUBTOTAL function can also handle nested subtotals, meaning that it can ignore other SUBTOTAL functions within the references. This can be useful for creating multiple levels of subtotals in a data set.

For example, suppose you have a data set like this:

You can use the SUBTOTAL function to create subtotals for each region and a grand total for the entire data set. To do this, you need to follow these steps:

• Sort the data by the Region column in ascending order. You can do this by selecting the data, going to the Data tab, and clicking on the Sort icon in the Sort & Filter group.
• Insert a blank row after each region. You can do this by selecting the row below each region, right-clicking, and choosing Insert from the context menu.
• In the blank row, enter the formula =SUBTOTAL(9,B2:B4) in the Sales column. This will return the sum of the sales for the North region. Drag the formula to fill the rest of the blank rows. This will create subtotals for each region.
• Insert another blank row at the end of the data set. In this row, enter the formula =SUBTOTAL(9,B2:B16) in the Sales column. This will return the sum of the sales for the entire data set. This is the grand total.

The result will look like this:

Notice that the SUBTOTAL function ignores the other SUBTOTAL functions in the references, and only sums the original values. This prevents double-counting the subtotals.

You can also hide or unhide any region by selecting the rows and right-clicking on them. The subtotals and the grand total will adjust accordingly, depending on the function_num argument. For example, if you hide the South region, the grand total will change from 7800 to 6300 if you use 109 as the function_num, or it will remain 7800 if you use 9 as the function_num.