Highest Date based on a number of criteria

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.
 
J

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
 
J

Jacob Skaria

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

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count based on date criteria 1
Finding most recent date for each customer - XL 2007 2
Count based on multiple criteria 2
count a value in a date range based on a date in another workshee. 4
Add numbers with a DATE as one of the conditions 5
formulas to count dates 2
mutliple criteria vlookups 5
Number of hrs worked in a week, based on dates in a different column 1

Top