Highest Date based on a number of criteria

  • Thread starter Thread starter jball
  • Start date Start date
J

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.
 
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
 
and format the formula cell to excel date format....

If this post helps click Yes
 
Back
Top