Practical Excel skills sharing: How to ignore hidden columns and perform sums?

In the previous article " Sharing of Practical Excel Skills: Using "Conditional Formatting" and "Function Formula" Together", we learned about "Conditional Formatting" and "Function Formula" through 2 examples. With the use of. Today we will talk about data summation and introduce the method of summing by ignoring hidden columns. Come and learn!

In our daily work, we often use summation. This problem is simple for everyone. It can be solved by using the SUM function:

Sometimes a few rows of data may be hidden. In this case, the SUBTOTAL function is used for summation. When there is no hiding, the SUBTOTAL function result is consistent with the SUM function calculation result, as follows:

Once we add certain rows (such as 3, 6, 9 , 12 rows) the data is hidden, and the result changes, as follows:

Note that the first parameter of the SUBTOTAL function uses 109 to indicate that the sum of hidden rows is ignored.

Regarding the SUBTOTAL function, tutorials have been published before. Friends who are interested can read the historical articles.

The problem we are going to discuss today is not how to sum if you ignore hidden rows, but how to sum if you ignore hidden columns. First of all, we must make it clear that the SUBTOTAL function cannot do this. It is clearly stated in the help of the function:

Not only can the SUBTOTAL function not do this, but at present For example, Excel does not yet have a function that ignores hidden columns for summing. So how to deal with this demand? This requires the use of a relatively new function - the CELL function to assist.

I believe that not many friends have seen this function, and even fewer know how to use it. Let’s first briefly understand what the CELL function is used for. Enter =c in the cell and you will see this function. After selecting the function, a simple explanation will appear:

In this sentence, you can roughly understand Go to the CELL function to get the format, location and other information of a cell. Double-click this function, and some options will appear:

As you can see, the function has two parameters, info_type and reference. The first parameter info_type, information type, there are 12 types in total. The specific meaning of each type can be understood through the function:

Friends who are interested in these information types can do so by themselves Take a look, they are all very easy to understand. The most important thing to use today is the last information type "width", which is simply the column width.

Some friends may have thought that if a column is hidden, its column width will be 0. Is this true? We can give it a try. Select "width" as the first parameter of the formula, and set the second parameter to B1, which means you want to get the column width of cell B1 (actually the column width of column B). Enter the formula in cell B16: =CELL("width", B1), and then pull the formula to the right:

The result is all 8. We can try adjusting the width of individual columns and see if there is any change:

When we adjust the width, the result is still 8. Is there something wrong with the formula?

In fact, no, the reason is that the CELL function is a bit temperamental. When the format of the cell changes (column width is a format), it must be recalculated to update the result. There are two ways to recalculate, one is to press the F9 function key, the other is to double-click any cell and press Enter. Take a look again and you will find that the result has been updated:

The size of the number does correspond to the width of the cell. Speaking of how to ignore hidden column sums, the answer is already obvious: use the CELL function to get the column width, and then use the SUMIF function to implement the sum. Enter the formula in cell I2 as: =SUMIF($B$16:$G$16,">0",B2:G2), and then pull the formula down.

When there is no hiding, all are summed. Now let’s hide a few columns to see the effect:

Remember to press F9 or double-click after hiding.

The problem seems to end here, but there are always some partners who are not willing to use auxiliary columns (auxiliary rows), and want to use array formulas to achieve it, for example: =SUMPRODUCT((CELL("width",B1 :G1>0)*B2:G2)

The idea seems reasonable, but this is not possible because if the parameter reference is a certain cell range, the function CELL will only return the information to the cell range. The cell in the upper left corner of the area. In other words, although I wrote a range like B1:G1, I got only the column width of B1.

Do you think the temper of the cell function is quite strange~~~

In fact, there are many interesting uses of this function. If you want to know, leave a message below!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: How to ignore hidden columns and perform sums?. For more information, please follow other related articles on the PHP Chinese website!