Autofilter with Subtotal Sumif

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Debra Dalgleish]

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"),--(D2200="Yes"),(E2:E200),(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Hi Bob

You are quiet correct your formula will give me 25 filtering by "Sheri"
In my reply to your first post I included the line;

I need to Filter on "Sheri" to show all her data and sum only her "No" Amount.

To clarify my aims.
Which are to filter on any name.
See a complete list of that name's data.
See a sum of that name's No (not paid) amounts which in my example would be
$15.00 for Sheri and $25.00 for Maureen.

You could 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

I was hoping to use the cell value from Tom Ogilvy's formula in place of the
name Sheri, Maureen or any name in Laurent Longre's formula to show amount
owing.

Below is what I was hoping to acheive by filtering in the name column only.

Small example of filtered data

Name Amount Paid
Sheri $10.00 Yes
Sheri $15.00 No


Sheri $15.00

--
Thank you once again

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

 

[Bob Phillips]

My apologies, I read that No as number. Stupid I know, but I did.

You still do not need to pick up the value from Tom's UDF as the filter will
ensure it is only Sheri, what you need is to include the No test in the
subtotal. That means you need a more complex formula. This should do it

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N
o"))

assuming the amounts are in column B, the Yes/No flag in column C. headings
in row 1, data rows 2-20.

--

HTH

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

 

[Guest]

Bob

No apology required on your part.

I really need to ask in my first post everthing I require, clearly with
correct criteria/information

Your formula is working just great.

--
Thank you

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

 

[Bob Phillips]

Glad we got there Aussie Bob.

Regards

UK Bob

Bob

No apology required on your part.

I really need to ask in my first post everthing I require, clearly with
correct criteria/information

Your formula is working just great.

--
Thank you

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

 

[Guest]

The formula that you provided works great. I now need to modify so it can be
used to add up data that would be equal to a wild card character. For
example I want to sum data that would be equal to *Bird* where this would
pull data such as Birdbath and Bluebird and just plain Bird. Right now the
formula returns 0.

I will be on vacation for a week so I will be unable to check out any
recommendations till my return.

Thanks,

Mitch