A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Summing Just the Visible Cells



 
 
Thread Tools Display Modes
  #1  
Old July 21st, 2004, 06:14 PM
CARL
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

If the auto-filter is engaged, is there a way to quickly
sum just the visible cells ?

Thanks in advance.
  #2  
Old July 21st, 2004, 06:35 PM
AlfD
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

Hi!

The quickest (but ephemeral) way is to read it in the Status Bar
(bottom right). Select the column you want to total and read off the
total of the visible items. Works for count, average, max etc.

Alf


---
Message posted from http://www.ExcelForum.com/

  #3  
Old July 21st, 2004, 07:24 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

Hi
use SUBTOTAL for this.
e.g.
=SUBTOTAL(9,A1:A10)

--
Regards
Frank Kabel
Frankfurt, Germany

"CARL" schrieb im Newsbeitrag
...
If the auto-filter is engaged, is there a way to quickly
sum just the visible cells ?

Thanks in advance.


  #4  
Old July 27th, 2004, 01:17 AM
lindasf
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

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 some
rows.

Thx. lindasf


---
Message posted from http://www.ExcelForum.com/

  #5  
Old July 27th, 2004, 05:41 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

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


--
Regards
Frank Kabel
Frankfurt, Germany


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
some rows.

Thx. lindasf


---
Message posted from http://www.ExcelForum.com/


  #6  
Old July 28th, 2004, 12:01 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

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)


"lindasf " wrote:

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 some
rows.

Thx. lindasf

---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

  #7  
Old July 28th, 2004, 02:44 AM
Bob Umlas
external usenet poster
 
Posts: n/a
Default Summing Just the Visible Cells

If you have Excel 2003, you can use =SUBTOTAL(109,A1:A100) which will add up
VISIBLE cells only.
"Dave Peterson" wrote in message
...
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)


"lindasf " wrote:

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 some
rows.

Thx. lindasf

---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SUBTOTAL Second Count / sub-count of Filtered Visible Cells QTE Worksheet Functions 3 July 21st, 2004 11:50 PM
Subtotal: Min / Max Offset on Filtered Visible Cells? QTE Worksheet Functions 4 July 5th, 2004 09:44 PM
Help summing only certain cells Russ H Worksheet Functions 2 June 17th, 2004 06:10 PM
Summing marked cells Sam Worksheet Functions 2 October 2nd, 2003 11:59 PM
COUNT visible cells? Gregg Stone Worksheet Functions 3 September 22nd, 2003 03:07 PM


All times are GMT +1. The time now is 08:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.