Hi Rob
yes, you can use the following array formuls to return the min & max dates
=MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
=MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
-- need to format these as dates
to return the min and max sales
=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))
where
-Sheet1!$B$1:$B$13 is the sales figures on your first sheet
-A2&B2 is the account number and min date on this sheet
-Sheet1!$A$1:$A$13 is the account number on your first sheet
-Sheet1!$C$1:$C$13 is the min date on your first sheet
the formula for max sales would be
=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))
all of these formulas are array formuls so enter using control & shift &
enter not just enter
--
Cheers
JulieD
check out
www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Rob" wrote in message
...
Hi,
I have a list of customer account numbers each with a sales value and date
e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for
each account no.
In another sheet, I have the account number shown once and want to show
alongside this the min and max date with sale i.e. columns A = AccNo, B =
MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max).
Do I use and array formula?
Thanks, Rob