C
CARL
If the auto-filter is engaged, is there a way to quickly
sum just the visible cells ?
Thanks in advance.
sum just the visible cells ?
Thanks in advance.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
AlfD
Hi!
The quickest (but ephemeral) way is to read it in the Status Ba
(bottom right). Select the column you want to total and read off th
total of the visible items. Works for count, average, max etc.
Al
The quickest (but ephemeral) way is to read it in the Status Ba
(bottom right). Select the column you want to total and read off th
total of the visible items. Works for count, average, max etc.
Al
F
Frank Kabel
Hi
use SUBTOTAL for this.
e.g.
=SUBTOTAL(9,A1:A10)
use SUBTOTAL for this.
e.g.
=SUBTOTAL(9,A1:A10)
L
lindasf
Frank,
When I use subtotal, it sums all the cells not just the visible ones.
I will admit, I am not using auto-filter, but have manually hidden som
rows.
Thx. lindas
When I use subtotal, it sums all the cells not just the visible ones.
I will admit, I am not using auto-filter, but have manually hidden som
rows.
Thx. lindas
F
Frank Kabel
Hi
if you have Excel 2003 use
=SUBTOTAL(109,A1:A100)
Before that version you'll need VBA to check the hidden state within a
user defined function
if you have Excel 2003 use
=SUBTOTAL(109,A1:A100)
Before that version you'll need VBA to check the hidden state within a
user defined function
D
Dave Peterson
You could use a User Defined Function like:
Option Explicit
Function sumVisible(rng As Range) As Double
Application.Volatile
Dim mySum As Double
Dim myCell As Range
mySum = 0
For Each myCell In rng.Cells
With myCell
If .EntireRow.Hidden _
Or .EntireColumn.Hidden _
Or Application.IsNumber(.Value) = False Then
'do nothing
Else
mySum = mySum + .Value
End If
End With
Next myCell
sumVisible = mySum
End Function
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
=======
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Type =sumVisible(A1:b9)
But be a little careful with this kind of function. It updates each time excel
recalculates. So if you hide some rows/columns and excel doesn't recalc, then
your results may be off--until the next recalc.
You can force a recalc by:
tools|options|calculate tab|click the "Calc now" button
(or just hit F9)
Option Explicit
Function sumVisible(rng As Range) As Double
Application.Volatile
Dim mySum As Double
Dim myCell As Range
mySum = 0
For Each myCell In rng.Cells
With myCell
If .EntireRow.Hidden _
Or .EntireColumn.Hidden _
Or Application.IsNumber(.Value) = False Then
'do nothing
Else
mySum = mySum + .Value
End If
End With
Next myCell
sumVisible = mySum
End Function
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
=======
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Type =sumVisible(A1:b9)
But be a little careful with this kind of function. It updates each time excel
recalculates. So if you hide some rows/columns and excel doesn't recalc, then
your results may be off--until the next recalc.
You can force a recalc by:
tools|options|calculate tab|click the "Calc now" button
(or just hit F9)
B
Bob Umlas
If you have Excel 2003, you can use =SUBTOTAL(109,A1:A100) which will add up
VISIBLE cells only.
VISIBLE cells only.