Array formula with column reference making macro too slow

[aldousjg]

Hi all,

I'm new to using array formulas so I am struggling to work out how to speed up an array formula.

My array formula returns the latest referral date against each customer (column A stores the unique customer ID and column U stores referral dates):

{=MAX(IF('ACP Data'!A:A=$A2,'ACP Data'!$U:$U))}

I'm assuming it is slow because it references whole columns, but my attempt to replace A:A with $A$2:$A$3000 resulted in an #N/A error.

The array formula is stored within a formulas sheet, then copied into cell A2 in another sheet and then autofilled down to the last row which is usually about 2000/3000.

Could anyone help with this?

Thanks,

 

[Fluff]

As you have 365 you can use the maxifs function instead

=MAXIFS('ACP Data'!$U$2:$U$5000,'ACP Data'!A$2:A$5000,$A2)

 

[aldousjg]

Thank you, much simpler!

 

[Fluff]

You're welcome & thanks for the feedback.