| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Latest date with condition
I have 3 columns, example:
Col A Col B Col C Row 1 Name Category of Sale Date of Sale Row 2 Jane 4 07/25/07 Row 3 Jane 5 10/17/07 Row 4 Jane 4 11/11/07 Row 5 Jane 5 06/30/07 I want to bring back the Name (Jane) and latest Date of an invididual's latest sale from a specific category. (Information is not entered in date order.) I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me the date of Jane's latest sale, nonspecific of which department. Now, how can I bring back 11/11/07 for Jane's latest sale of Category 4? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Latest date with condition
try
=INDEX(H4:H14,MATCH(MAX(IF(G4:G14="Jane",I4:I14)), I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I have 3 columns, example: Col A Col B Col C Row 1 Name Category of Sale Date of Sale Row 2 Jane 4 07/25/07 Row 3 Jane 5 10/17/07 Row 4 Jane 4 11/11/07 Row 5 Jane 5 06/30/07 I want to bring back the Name (Jane) and latest Date of an invididual's latest sale from a specific category. (Information is not entered in date order.) I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me the date of Jane's latest sale, nonspecific of which department. Now, how can I bring back 11/11/07 for Jane's latest sale of Category 4? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Latest date with condition
Thanks, Don for your quick response. I may be missing something, but I
believe I need Category 4 ID'd somehow. I have hundreds of names and ten different categories. "Don Guillett" wrote: try =INDEX(H4:H14,MATCH(MAX(IF(G4:G14="Jane",I4:I14)), I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I have 3 columns, example: Col A Col B Col C Row 1 Name Category of Sale Date of Sale Row 2 Jane 4 07/25/07 Row 3 Jane 5 10/17/07 Row 4 Jane 4 11/11/07 Row 5 Jane 5 06/30/07 I want to bring back the Name (Jane) and latest Date of an invididual's latest sale from a specific category. (Information is not entered in date order.) I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me the date of Jane's latest sale, nonspecific of which department. Now, how can I bring back 11/11/07 for Jane's latest sale of Category 4? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Latest date with condition
Sorry, I misread and was giving you the number 4 as a result when your
request was easier to get the DATE You wanted to know the latest date for cat 4 and Jane.CSE and format as date =MAX(IF((G4:G14="Jane")*(H4:H14=4),I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I see now what your formula does. It will bring back the category itself (4), or at least from how I interpolated your columns with mine. I've tried different combos to bring the date itself but haven't succeeded. "Don Guillett" wrote: try =INDEX(H4:H14,MATCH(MAX(IF(G4:G14="Jane",I4:I14)), I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I have 3 columns, example: Col A Col B Col C Row 1 Name Category of Sale Date of Sale Row 2 Jane 4 07/25/07 Row 3 Jane 5 10/17/07 Row 4 Jane 4 11/11/07 Row 5 Jane 5 06/30/07 I want to bring back the Name (Jane) and latest Date of an invididual's latest sale from a specific category. (Information is not entered in date order.) I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me the date of Jane's latest sale, nonspecific of which department. Now, how can I bring back 11/11/07 for Jane's latest sale of Category 4? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Latest date with condition
Perfect! Exactly what I needed.
Thanks! Casey "Don Guillett" wrote: Sorry, I misread and was giving you the number 4 as a result when your request was easier to get the DATE You wanted to know the latest date for cat 4 and Jane.CSE and format as date =MAX(IF((G4:G14="Jane")*(H4:H14=4),I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I see now what your formula does. It will bring back the category itself (4), or at least from how I interpolated your columns with mine. I've tried different combos to bring the date itself but haven't succeeded. "Don Guillett" wrote: try =INDEX(H4:H14,MATCH(MAX(IF(G4:G14="Jane",I4:I14)), I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I have 3 columns, example: Col A Col B Col C Row 1 Name Category of Sale Date of Sale Row 2 Jane 4 07/25/07 Row 3 Jane 5 10/17/07 Row 4 Jane 4 11/11/07 Row 5 Jane 5 06/30/07 I want to bring back the Name (Jane) and latest Date of an invididual's latest sale from a specific category. (Information is not entered in date order.) I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me the date of Jane's latest sale, nonspecific of which department. Now, how can I bring back 11/11/07 for Jane's latest sale of Category 4? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Latest date with condition
Glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... Perfect! Exactly what I needed. Thanks! Casey "Don Guillett" wrote: Sorry, I misread and was giving you the number 4 as a result when your request was easier to get the DATE You wanted to know the latest date for cat 4 and Jane.CSE and format as date =MAX(IF((G4:G14="Jane")*(H4:H14=4),I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I see now what your formula does. It will bring back the category itself (4), or at least from how I interpolated your columns with mine. I've tried different combos to bring the date itself but haven't succeeded. "Don Guillett" wrote: try =INDEX(H4:H14,MATCH(MAX(IF(G4:G14="Jane",I4:I14)), I4:I14)) -- Don Guillett Microsoft MVP Excel SalesAid Software "casey" wrote in message ... I have 3 columns, example: Col A Col B Col C Row 1 Name Category of Sale Date of Sale Row 2 Jane 4 07/25/07 Row 3 Jane 5 10/17/07 Row 4 Jane 4 11/11/07 Row 5 Jane 5 06/30/07 I want to bring back the Name (Jane) and latest Date of an invididual's latest sale from a specific category. (Information is not entered in date order.) I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me the date of Jane's latest sale, nonspecific of which department. Now, how can I bring back 11/11/07 for Jane's latest sale of Category 4? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| latest date | New Users to Excel | |||
| X axis date - display beyond latest date. | Charts and Charting in Excel | |||
| Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
| Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
| sumproduct for latest date | Excel Worksheet Functions | |||
Linear Mode
