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

		9/1	9/2	9/3	9/4
A	B	1			1
A	Y				1

<tbody>
</tbody>

Sheet2

P	1st Date	2nd Date	Status
B	9/1	9/4	A
Y	9/2	9/3
B	9/1		A
B	9/4
Y	9/2	9/4	A

<tbody>
</tbody>

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.