Max and Min date from listing by account no.

[Rob]

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

 

[JulieD]

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

 

[Rob]

Thanks Julie for quick reply, I'll try this out now. Rob

 

[JulieD]

let us know how you get on