Highest Date based on a number of criteria
 

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.

Highest Date based on a number of criteria
 

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

--
If this post helps click Yes
---------------
Jacob Skaria


"jball" wrote:

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.

Highest Date based on a number of criteria
 

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

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"jball" wrote:

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.

Highest Date based on a number of criteria
 

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

Just press ENTER


"jball" wrote:

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.