How do you let hidden data not participate in the sum calculation in Excel?

When Excel sums the hidden data so that it does not participate in the calculation, the SUM function, whether or not hidden, lets the data participate in the sum, and if you sum the SUBTOTAL function, you can sum the hidden areas. The following small series for everyone to introduce, a look at it!

The data stored in A1 10 and B1 to B10 cells are numeric 1, as shown in the figure. Enter the function =sum (a1:a10) in A11, and the return value of the SUM function is 10.

Click the 1 Line tab to drag down to the 5-line label, then right-click and choose Hide.

The return value of the SUM function remains 10 after the 5 line is hidden.

The function in the B11 cell is the =subtotal (109,B1:B10) set so that the function is to sum the second parameter range in addition to the hidden region.

At this point, the 1 to 9 lines are all hidden, at which point the B11 cell return value of the input subtotal function becomes 1, where only the B10 that are not hidden from the B1 to the B10 are summed.

When you view B11 cells after you unhide them all, the B11 cell now becomes 10 because there are no hidden cells in B1 to B10.

Note: Remember that the first argument is 109 before you can sum a range of not-hidden cells

The above is how to make the hidden data in Excel not to participate in the sum calculation method introduction, hope to be helpful to everyone!