Summing visible cells only

Tim Grollman · Apr 26th, 2002, 08:20 AM

I want to sum just the cells I can see when I have a filter on. Do I need to have a column which contains a 1 when the the row is visible and a zero otherwise, and use SUMPRODUCT or is there an easier way? Thanks

Mark W. · Apr 26th, 2002, 08:21 AM

Have you tried using the SUBTOTAL worksheet function?

Tim Grollman · Apr 26th, 2002, 08:27 AM

Yes, I don't want to use subtotals in this job. Is there another way?

Aladin Akyurek · Apr 26th, 2002, 08:29 AM

Try to use the filter criteria directly in a SUMPRODUCT formula if you must.

Jay Petrulis · Apr 26th, 2002, 08:44 AM

Hi,

Try this UDF...

Function SumVisible(RefRange As Range)
Dim UsedCell As Range, temp As Double
Application.Volatile

For Each UsedCell In RefRange
If Not UsedCell.EntireRow.Hidden And Not UsedCell.EntireColumn.Hidden Then
If IsNumeric(UsedCell.Value) Then
temp = temp + UsedCell.Value
End If
End If
Next UsedCell
SumVisible = temp
End Function

Bye,
Jay

EDIT: Added Application.Volatile to make it more accurate, but hiding and unhiding rows and columns is not a trappable event, I believe, so be careful when (if) using this.

[ This Message was edited by: Jay Petrulis on 2002-04-26 09:29 ]

Chris Davison · Apr 26th, 2002, 05:46 PM

Tim,

I'd go with Mark's =SUBTOTAL function suggestion, it's different from data / subtotals

it will sum only visible cells when filtering is on - changing the filter criteria will result in a new sum....

Apr 26th, 2002, 05:46 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Tim, I'd go with Mark's =SUBTOTAL function suggestion, it's different from data / subtotals it will sum only visible cells when filtering is on - changing the filter criteria will result in a new sum.... __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 08:20 AM	#1
Tim Grollman Board Regular Join Date: Feb 2002 Posts: 129	I want to sum just the cells I can see when I have a filter on. Do I need to have a column which contains a 1 when the the row is visible and a zero otherwise, and use SUMPRODUCT or is there an easier way? Thanks

Apr 26th, 2002, 08:21 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Have you tried using the SUBTOTAL worksheet function?

Apr 26th, 2002, 08:27 AM	#3
Tim Grollman Board Regular Join Date: Feb 2002 Posts: 129	Yes, I don't want to use subtotals in this job. Is there another way?

Apr 26th, 2002, 08:29 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	Try to use the filter criteria directly in a SUMPRODUCT formula if you must.

Apr 26th, 2002, 08:44 AM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, Try this UDF... Function SumVisible(RefRange As Range) Dim UsedCell As Range, temp As Double Application.Volatile For Each UsedCell In RefRange If Not UsedCell.EntireRow.Hidden And Not UsedCell.EntireColumn.Hidden Then If IsNumeric(UsedCell.Value) Then temp = temp + UsedCell.Value End If End If Next UsedCell SumVisible = temp End Function Bye, Jay EDIT: Added Application.Volatile to make it more accurate, but hiding and unhiding rows and columns is not a trappable event, I believe, so be careful when (if) using this. [ This Message was edited by: Jay Petrulis on 2002-04-26 09:29 ]