Dragging forumla freezing excel

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
473
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top