Calculation process for expanding data rows

[Dahlia]

Hi All,

The formula below works just great for me but the calculation will not move at all when the data rows is more than 1k. Can somebody help to amend the below code to allow me to have what I need regardless the data rows, please? This formula is actually calculates number of staff who access the same system on the same date. Please take note, the dates are not in chronological manner and not possible to change because it will impact other formula's output if done so. I supposed that would be one of the factors why the calculation will take longer than it should but still possible as long as less than 1k.

with Ctrl+Shift+Enter
=IF($J49="","",SUM(IFERROR(SIGN(LEN($J$2:$J$439))/COUNTIFS($J$2:$J$439,$J$2:$J$439&"",$U$2:$U$439,$U$2:$U$439,$U$2:$U$439,$U49),0)))

Or if anyone can advise me other mechanisms (other than manual count) then am open for suggestions.

Thank you in advance.

DZ

[Kaper]

Such formulas usually work quicker, when not done in one pass, but splitted into shorter non-array formulas calculated in some (may be hidden) helper columns. Try this approach.

Other idea could be to use a macro, which will result in additional benefit of not being recalculated every time anything changes in a sheet, but only per request.