Formula to get last date if customer is XXX

[drom]

Hi and thanks in advance!


I would like to know when was last time I have had a meeting with a customer

I am using a table where Customer is in column A A5:500 and DATES are in column B B5:B500
Header in my tables are in:

• A4 = Customer
• B4 = DATE

Name of the table Table1 (in case needed)

• A4:B500

I would like to have the formula in B2

Usually the dates are sorted in ascending order (in case matters)

I am going to put the target Customer in A2 and get the desired result in B2

I do not want to use VBA/Macros

 

[dermie_72]

maxifs is the formula you are looking for.

 

[PeteWright]

If your data is sorted:

Book0001.xlsm
	A	B
1	Customer?	Result:
2	Jason	2/7/2024
3
4	Customer	Date
5	Frank the Tank	2/1/2024
6	Jason	2/2/2024
7	Edward Scissorhands	2/3/2024
8	Jason	2/4/2024
9	Freddy Kruger	2/5/2024
10	Jack the Ripper	2/6/2024
11	Jason	2/7/2024
Sheet10

Cell Formulas
Range	Formula
B2	B2	= MAXIFS(B5:B500,A5:A500,"="&A2)

 

[PeteWright]

Should also work if unsorted.

 

[dermie_72]

Hi and thanks in advance!


I would like to know when was last time I have had a meeting with a customer

I am using a table where Customer is in column A A5:500 and DATES are in column B B5:B500
Header in my tables are in:

• A4 = Customer
• B4 = DATE

Name of the table Table1 (in case needed)

• A4:B500

I would like to have the formula in B2

Usually the dates are sorted in ascending order (in case matters)

I am going to put the target Customer in A2 and get the desired result in B2

I do not want to use VBA/Macros

Any feedback Drom?