How to prevent hidden data from participating in sum calculation in Excel

How to prevent hidden data from participating in sum calculation in Excel? This problem bothers many users. PHP editor Xiaoxin will introduce a simple and practical operation method in detail to help you solve this problem. Next, please continue reading this article to learn the specific steps.

As shown in the figure, the data stored in cells a1 to ah 10 and b1 to b10 are all 1. Enter the function =sum(a1:a10) in a11, and the return value of the summation function is 10.

Click the 1-line label and drag it down to the 5-line label, then right-click and select Hide.

After hiding 5 rows, the return value of the sum function is still 10.

The function in cell b11 is set like this =subtotal(109,B1:B10). Its function is to sum the second parameter area except the hidden area.

At this time, all rows 1 to 9 are hidden. At this time, the return value of cell b11 entered into the subtotal function becomes 1. At this time, only the unhidden rows from B1 to B10 are B10 sums.

After unhiding all, check the b11 cell. Now the b11 cell becomes 10, because there are no hidden cells in B1 to B10.

Note: Remember that the first parameter is 109 before you can sum the non-hidden cell area

The above is the detailed content of How to prevent hidden data from participating in sum calculation in Excel. For more information, please follow other related articles on the PHP Chinese website!