Dragging forumla freezing excel

[ste33uka]

Hi i need to drag this forumla on excel to the 500,000 line,
iv tried dragging it in stages , 20,000 lines at a time, but excel always freezes around the 200,000 line
anyone any ideas why , or any other formula similar that would work?
iv tried dragging formula and filling formula down
Thanks

=IF(A77<>"",COUNTIFS($D$76:D77,D77,$Y$76:Y77,Y77,$AK$76:AK77,AK77,$AA$76:AA77,AA77),0)

 

[awoohaw]

500,000 records is really big, and you're reading 4 columns nearly that long.
Have you considered doing the work in Power Query or Power Pivot?

 

[ste33uka]

500,000 records is really big, and you're reading 4 columns nearly that long.
Have you considered doing the work in Power Query or Power Pivot?

i dont want to use Power Query or Power Pivot, i have 32gb ram, should not be a probelm 500,000 lines

 

[awoohaw]

If you say so. Just that column has over 2.5 Million computations, 4 references in each cell scan 500,000 cells with each computation.
It may not be your RAM, but your processor. Have you checked your task manager resource utilization?

 

[Alex Blakenburg]

I agree with @awoohaw that is an unworkable number of computations. If you don't want to use Power Query to do the heavy lifting then your other option is going to be to use VBA to populate the counter column which I can see you have used in the past. The code would return just the results as values