|
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? |
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? |
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? |
Latest date with condition
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? |
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? |
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? |
| All times are GMT +1. The time now is 07:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com