Show Last Date if conditions are met

[Colmans]

Hi

I need to show the date of last activity on a report. In my head, I should use the IF function but cant get it to work. My logic is as follows:

=IF(Retailer_Name,D7,MAX(App_Date))

Retailer_Name = Retailers names as defined as a data range in worksheet
App_Date = date of application defined as a range

I'd also like to add an additional condition e.g Status = "Completed)

SO I have two collums, one which shows the last date of an application for the supplier, and one that shows the last date of a completed transaction for the supplier.

Thanks in advance for feedback.

 

[ParamRay]

Hello, I've assumed: (1) your retailer names are in A2:A17; (2) your application dates are in B2:B17; and (3) your statuses are in C2:C17.

Last application date goes in cell D2, then filled down:

{= MAX(($A$2:$A$17 = $A2) * $B$2:$B$17)}

Last completed application date goes in cell E2, then filled down:

{= MAX(($A$2:$A$17 = $A2) * ($C$2:$C$17 = "Completed") * $B$2:$B$17)}

Note these are array formulas, so don't type the curly braces. Just enter them with Ctrl + Shift + Enter.

 

[Colmans]

Thanks ParamRay for your response, but the date being returned is showing as 00/01/1900

I've also found an alternative, which strangely gives me the same answer 00/01/1900

{=MAX(IF(Retailer_Name=D2,IF(App_Status="Completed",App_Date)))}

 

[Colmans]

OK the following works, i'd not added the array in my worksheet

{=MAX(IF(Retailer_Name=D2,IF(App_Status="Completed",App_Date)))}

However, its only when there has been no activity against the retailer, it returns the 00/01/1900. What can I do to amend the formula e.g "No Activity" in the value for false. I've tried a number of combinations and its not liking them.

Thanks

 

[Dave Patton]

Try either of the 2 suggestions shown below.

Excel 2010
	A	B	C	D	E	F
1		Retailer		ABC	2-Mar-17	2-Mar-17
2				ZZZ
3
4	date	Retailer	Status
5	1-Mar-17	ABC	OK
6	1-Mar-17	DEF	Completed
7	1-Mar-17	GGG	OK
8	2-Mar-17	ABC	Completed
9	2-Mar-17	DEF	OK
10	2-Mar-17	GGG	Completed
11	12-Mar-17	ABC	OK
12	12-Mar-17	SSS	Completed
13	12-Mar-17	SSS	XX
14
1cc

Cell Formulas
Range	Formula
F1	=IF(COUNTIF($B$5:$B$13,D1),AGGREGATE(14,6,A$5:A$13/(B$5:B$13=$D$1)*($C$5:$C$13="Completed"),ROWS($A$1:$A1-$A$1+1)),"")
F2	=IF(COUNTIF($B$5:$B$13,D2),AGGREGATE(14,6,A$5:A$13/(B$5:B$13=$D$1)*($C$5:$C$13="Completed"),ROWS($A$1:$A2-$A$1+1)),"")
E1	{=MAX(IF($B$5:$B$13=D1, IF($C$5:$C$13="Completed", $A$5:$A$13)))}
E2	{=MAX(IF($B$5:$B$13=D2, IF($C$5:$C$13="Completed", $A$5:$A$13)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

If the Retailer does not exist, the formula will yield 0.
To suppress the 0, uncheck the option to show a zero with zero values located in Options ! Advanced ! Display