Re: Autofilter with Subtotal Sumif

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Bob Phillips" wrote:

Glad we got there Aussie Bob.

Regards

UK Bob

"Robert Christie" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:452B631E-27FF-43CE-A0F0-6AA780603F28@xxxxxxxxxxxxxxxx
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" wrote:

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)


"Robert Christie" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F7A36B00-9CC5-4725-8909-AA2E17751CBB@xxxxxxxxxxxxxxxx
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" wrote:

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" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:99FA04A3-F415-4DDB-A81E-A2D8F178A3F5@xxxxxxxxxxxxxxxx
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











.

Relevant Pages

  • Re: Inserting a HPGL Picture in Word 2007
    ... Thanks very much for your helpful reply Bob - ... The filter we used was "signed" byone of the two 3rd parties you mentioned ... The MS HP Graphics Language interpreter/ filter, HPGLIm32.FLT, was written ... WordPerfect also had an HPGL Import Filter written by Imagestream/Inso ...
    (microsoft.public.word.drawing.graphics)
  • RE: Problem with applying filter in a form
    ... Thanks for your help Bob. ... I got a form and trying to test a code by applying filter. ... Dim strwhere As String ...
    (microsoft.public.access.formscoding)
  • Re: Microsofts "New Coke"
    ... Wrong BOB. ... but subsequent versions of Windows have ... the filter expires and they are back to normal. ... I can't understand the necessity. ...
    (rec.boats)
  • Re: filter data by top 10 AND bottom 10 items simultaneously
    ... Hi Bob, thanks for advice but I can't get it to work - all the formulas ... In the example below should I write this formula in cell C6 ... I've used a macro to filter using advanced ... Row 5: Value Sector ...
    (microsoft.public.excel.worksheet.functions)
  • Re: [SLE] Spamassassin learning
    ... On Tue December 23 2003 03:06 am, Bob S. wrote: ... Please excuse me for being real dumb/stupid. ... >> as the header I want to filter on. ...
    (SuSE)