Max date only against met criteria

[Nelson]

I have 2 product lines each with 10 part numbers each

I pulled customers data and my profile sheet shows me what sub products of
the 2 main product lines the customer has purchased how much they purchased
the part number and their renewal date

What I want to do now have a formula show me the max renewal date for
customer X for each major product line, I am assuming I would use
(ISNUMBER(MATCH(B8:B32,C47:C85,0))),L8:L32) but I am unsure how I would
include the find max date if (ISNUMBER(MATCH(B8:B32,C47:C85,0))),L8:L32) is
met.

Any thoughs?

Thanks in advance

 

[Shane Devenshire]

Hi,

Whant not show us your basic data and what you want to return from that
sample?

However, this may do what you want: (an array)

=MAX(IF(($A$1:$A$22=F1)*($C$1:$C$22=E1),B1:B22))

In this case the Product Line is in A, the Customer in C and the date in B.
In E1:F1 are the customer and major product line you want to look at. Enter
a list of all the customers and product line combinations in column E:F and
copy the above formula down from G1.

array - you must enter the formula by pressing Shift+Ctrl+Enter

 

[T. Valko]

(ISNUMBER(MATCH(B8:B32,C47:C85,0))),L8:L32)

What's in L8:L32, your dates?

Try this array formula** :

=MAX(IF(ISNUMBER(MATCH(B8:B32,C47:C85,0)),L8:L32))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

 

[Ashish Mathur]

Hi,

You could try this

=max(index((B2:B80=B83)*(C2:C80=C83)*(L2:L80),,1))

B2:B80 holds customers, C2:C80 holds product line and L2:L80 holds dates.
B83 has customer X, C83 has desired product line.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Nelson]

OK I see I may have confused things a little

In my customer profile sheet I have

A8 - a18 which holds the renewal dates for the customer
B8 - B18 holds the part numbers (some can be for one product line and some
can be for the other)

C 47 - C60 holds the list for product line A
D 47 - D60 holds the list for product line B


I want to have 2 cells one will show me the max renewal date for product A
and another cell with show me the max renewal date from product B.

I know it will be the same formula, so the question what would the formula
be that would search against D8 - D18 for only products that match C47 - C60,
then look at the corresponding dates in A8 - A18 and give me the max date?

Hope that cleared it up and my apologies for the confusion.

Thanks

 

[T. Valko]

Try these array formulas** :

For product line A:

=MAX(IF(ISNUMBER(MATCH(B8:B18,C47:C60,0)),A8:A18))

For product line B:

=MAX(IF(ISNUMBER(MATCH(B8:B18,D4760,0)),A8:A18))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Nelson]

Thanks it works however, how do I get it to show nothing should a customer
only purchase one type of product. currently it shows a date of 1/0/1900 if
I have customers who only have one product line.

Thanks

 

[T. Valko]

Try these (array entered):

=IF(SUM(--ISNUMBER(MATCH(B8:B18,C47:C60,0))),MAX(IF(ISNUMBER(MATCH(B8:B18,C47:C60,0)),A8:A18)),"")

=IF(SUM(--ISNUMBER(MATCH(B8:B18,D4760,0))),MAX(IF(ISNUMBER(MATCH(B8:B18,D4760,0)),A8:A18)),"")