Hi

Small example of data

Name Amount Paid
Sheri $10.00 Yes
Sheri $15.00 No
Maureen $25.00 No
Maureen $12.00 Yes

If I AutoFilter the the list for Name "Sheri", how can I use the Subtotal
and Sumif functions together to show an answer of $15.00?
Or how can I use the Sumif or Sumproduct functions only on the visible cells?
Plus is it possible to show the filtered name "Sheri" in a seperate cell?

--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro

Just use

=SUBTOTAL(9,B:B)

To show the filtered name, use Tome Ogilvy's great ShowFilter UDF, and add
this to a cell

=MID(showfilter(A1)&CHAR(SUBTOTAL(9,A2)*0+32),2,9)

The UDF is

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet

Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert Christie" wrote in message
...
Hi

Small example of data

Name Amount Paid
Sheri $10.00 Yes
Sheri $15.00 No
Maureen $25.00 No
Maureen $12.00 Yes

If I AutoFilter the the list for Name "Sheri", how can I use the Subtotal
and Sumif functions together to show an answer of $15.00?
Or how can I use the Sumif or Sumproduct functions only on the visible
cells?
Plus is it possible to show the filtered name "Sheri" in a seperate cell?

--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro

Hi Bob

The =SUBTOTAL(9,B:B) would give an answer of $62.00.
I need to Filter on "Sheri" to show all her data and sum only her "No" Amount.

Do I place Tom Ogilvy's UDL in a Module or in the sheet?


--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro


"Bob Phillips" wrote:

Just use

=SUBTOTAL(9,B:B)

To show the filtered name, use Tome Ogilvy's great ShowFilter UDF, and add
this to a cell

=MID(showfilter(A1)&CHAR(SUBTOTAL(9,A2)*0+32),2,9)

The UDF is

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet

Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert Christie" wrote in message
...
Hi

Small example of data

Name Amount Paid
Sheri $10.00 Yes
Sheri $15.00 No
Maureen $25.00 No
Maureen $12.00 Yes

If I AutoFilter the the list for Name "Sheri", how can I use the Subtotal
and Sumif functions together to show an answer of $15.00?
Or how can I use the Sumif or Sumproduct functions only on the visible
cells?
Plus is it possible to show the filtered name "Sheri" in a seperate cell?

--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro

You would put Tom Ogilvy's code on a regular module.

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to sum cells in column E, where column D contain the value
"Yes", after a filter on another column, you could use the following,
where there are no blank cells in those rows in column A:

=SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFSET(A2:A2 00,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

Robert Christie wrote:
Hi Bob

The =SUBTOTAL(9,B:B) would give an answer of $62.00.
I need to Filter on "Sheri" to show all her data and sum only her "No" Amount.

Do I place Tom Ogilvy's UDL in a Module or in the sheet?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra

Could I change the "Sheri" part of Laurent Longre's formula to refer to the
cell value from Tom Ogilvy's code.
So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount.

I'm trying to just use the Autofilter with the one criteria and show all
"Sheri" data with the unpaid amount 3 row below.

Thanks you to both yourself and Bob Philips for your help.
--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro


"Debra Dalgleish" wrote:

You would put Tom Ogilvy's code on a regular module.

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to sum cells in column E, where column D contain the value
"Yes", after a filter on another column, you could use the following,
where there are no blank cells in those rows in column A:

=SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFSET(A2:A2 00,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

Robert Christie wrote:
Hi Bob

The =SUBTOTAL(9,B:B) would give an answer of $62.00.
I need to Filter on "Sheri" to show all her data and sum only her "No" Amount.

Do I place Tom Ogilvy's UDL in a Module or in the sheet?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

The formula that I gave you will do exactly what you ask for. It will show
62, then when you apply the filter, it will show 25. Debra's formula will
provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone
else, or not at all, which is not what you originally asked for.

You could I change the "Sheri" part of Laurent Longre's formula to refer to
the cell value from Tom Ogilvy's code, but it would be totally pointless.
Try the formula I gave you, apply a filter, and then tell me it doesn't
work. I am looking at an example now that is filtered by Maureen, Tom's UDF
shows Maureen, the amount is 37. Lo and behold, I change the filter criteria
to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked
for.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert Christie" wrote in message
...
Hi Debra

Could I change the "Sheri" part of Laurent Longre's formula to refer to
the
cell value from Tom Ogilvy's code.
So if I filter on "Maureen" the values would reflect Maur3een's unpaid
amount.

I'm trying to just use the Autofilter with the one criteria and show all
"Sheri" data with the unpaid amount 3 row below.

Thanks you to both yourself and Bob Philips for your help.
--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro


"Debra Dalgleish" wrote:

You would put Tom Ogilvy's code on a regular module.

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to sum cells in column E, where column D contain the value
"Yes", after a filter on another column, you could use the following,
where there are no blank cells in those rows in column A:


=SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS
ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

Robert Christie wrote:
Hi Bob

The =SUBTOTAL(9,B:B) would give an answer of $62.00.
I need to Filter on "Sheri" to show all her data and sum only her "No"
Amount.

Do I place Tom Ogilvy's UDL in a Module or in the sheet?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html