Summing Only Visible Values
авторSummary: The SUM function doesn’t allow you to ignore hidden values in a range being summed. Instead, you are better off using the SUBTOTAL function or a special user-defined function, both of which are described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)
авторKirk is using the SUM function in many of his worksheets to (naturally) determine the sum of a range of values. The problem he is running into, however, is that the range he is summing contains some hidden rows, and he doesn't want those values--the hidden ones--included in the sum.
The SUM function is pretty simplistic in how it does its work; it simply sums a range. You can change the function you use and get the desired results, however. For instance, let's assume that you want to sum the range of A3:A45, and that you don't want any hidden values to be included in the sum. You should use the SUBTOTAL function in the following manner:
=SUBTOTAL(109,A3:A45)
авторThe first parameter of the function (109) indicates how you want SUBTOTAL to do its work. In this case, it means you want SUBTOTAL to sum the range, using the SUM function, and you don't want any hidden values included in the value returned. (You can find out more about the controlling SUBTOTAL parameters if you look in the online Help for the SUBTOTAL function.)
If you don't want to use the SUBTOTAL function for some reason, you can create your own user-defined function (a macro) that will only sum the visible values in a range. Consider the following macro:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Function Sum_Visible(Cells_To_Sum As Object)
Dim vTotal As Variant
Application.Volatile
vTotal = 0
For Each cell In Cells_To_Sum
If Not cell.Rows.Hidden Then
If Not cell.Columns.Hidden Then
vTotal = vTotal + cell.Value
End If
End If
Next
Sum_Visible = vTotal
End Function
авторTo use the function, simply use a formula like this wherever you want your sum to appear:
=Sum_Visible(A1:A1000)
авторThis macro is an adaptation of a macro found in Microsoft's Knowledge Base. You can find two articles pertinent to this tip at these locations:
http://support.microsoft.com/?kbid=150363
http://support.microsoft.com/?kbid=213313
сам савэт -
http://exceltips.vitalnews.com/Pages/T1215_Summing_Only_Visible_Values.html 
