Highest Date based on a number of criteria

[jball]

Hi all,

Say I sell ice creams from different stores and want to know the most recent
date a sale was made from a specified store and flavour.

Flavour Store Date
Chocolate A 1/09/2009
Chocolate B 1/09/2009
Vanilla A 1/09/2009
Vanilla A 2/09/2009

Eg, If Flavour = Vanilla, If Store = A, what is the most recent date a sale
was made.
I wish this to be a formula. Any suggestions?
Thanks.

 

[NBVC]

Try something like:

=Max(IF(Flavour_range=Flavour,IF(Store_range=Store,Date_Range))

replace range with corresponding ranges and flavour,store with actua
cell refs containing the criteria...

Then confirm with CTRL+SHIFT+ENTER not just ENTER.

You might need to format result cell as date

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Jacob Skaria]

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

With the below sample data

=MAX(IF((A2:A10>=D1)*(B2:B10=E1),C2:C10))

Col A Col B Col C Col D Col E
Flavour Store Date Vanilla A
Chocolate A 1/9/2009
Chocolate B 1/9/2009
Vanilla A 1/9/2009
Vanilla A 2/9/2009

 

[Jacob Skaria]

and format the formula cell to excel date format....

If this post helps click Yes

 

[Teethless mama]

=SUMPRODUCT(MAX((A2:A6="Vanilla")*(B2:B6="A")*C2:C6))

Just press ENTER