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