Replacing If with Max

[Velven]

Hello Guys ,

As I heard that Max formula is the quicker formula , I was thinking I would like to speed up my workbook.

This the sample.

Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]9/1[/TD]
[TD]9/2[/TD]
[TD]9/3[/TD]
[TD]9/4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]P[/TD]
[TD]1st Date[/TD]
[TD]2nd Date[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/1[/TD]
[TD]9/4[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]9/2[/TD]
[TD]9/3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/1[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]9/2[/TD]
[TD]9/4[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

Data will be in Sheet2 while calculation will be in C2:F3.

Original Formula in C2
This is an array formula
=SUM(IF(IF(Sheet2!$D:$D=$A$2,IF(Sheet2!$A:$A=$B$2,IF(Sheet2!$B:$B>=Sheet2!$C:$C,Sheet2!$B:$B,IF(Sheet2!$C:$C>=Sheet2!$B:$B,Sheet2!$C:$C))))=C1,1))

Would love to replace this statement to MAX , is it possible ?
"IF(Sheet2!$B:$B>=Sheet2!$C:$C,Sheet2!$B:$B,IF(Sheet2!$C:$C>=Sheet2!$B:$B,Sheet2!$C:$C))"

 

[Velven]

Bump

 

[Aladin Akyurek]

This IF bit

IF(Sheet2!$B:$B>=Sheet2!$C:$C,Sheet2!$B:$B,IF(Sheet2!$C:$C>=Sheet2!$B:$B,Sheet2!$C:$C))

creates an array result, which is needed here. MAX, however applied, does not.

1. By the way, it's not a good idea to run an array-processing formula on the whole columns. Use instead definite ranges or dynamic named ranges.

2. If the performance is an issue, try to create a helper range in Sheet2 which compares 1st date and 2nd date, using MAX. Once done so, the SUM+IF count formula can be simplified.