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