Max date only against met criteria
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,D47:D60,0))),MAX(IF(ISNUMBER (MATCH(B8:B18,D47:D60,0)),A8:A18)),"")
--
Biff
Microsoft Excel MVP
"Nelson" wrote in message
...
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
--
Nelson
"T. Valko" wrote:
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,D47:D60,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.
--
Biff
Microsoft Excel MVP
"Nelson" wrote in message
...
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
--
Nelson
"Shane Devenshire" wrote:
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
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Nelson" wrote:
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
--
Nelson
|