Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
(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 -- Biff Microsoft Excel MVP "Nelson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max date only against met criteria
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sum up values in the date criteria | Excel Discussion (Misc queries) | |||
sumproduct 3 criteria inc. date | Excel Worksheet Functions | |||
using a date in criteria for SUMIF | Excel Discussion (Misc queries) | |||
IF with date criteria | New Users to Excel | |||
Counting with date criteria | Excel Worksheet Functions |