Glad we got there Aussie Bob.
Regards
UK Bob
"Robert Christie" wrote in message
...
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" wrote in message
...
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" 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