I am running into problems working with countifs formulas. The data I'm working with are over 150K+ rows. I'm having to copy and paste the formulas in chunks of about 30K, then go back and paste values. Even while doing this, I'm waiting 10 minutes for the formulas to calculate. Excel ends up not responding half the time and I have to shut down and restart the program. Any suggestions on how to avoid this or work more efficiently? My data will continue to grow throughout the fiscal year, I could spend 2 days just doing calculation in the workbook!
Copy and pasting formulas for LARGE amounts of data
- Thread starter gtothek
- Start date
Samuel Cruz
New Member
Hello gtothek.
I had a similar problem. But I solved it by using linked workbooks. I work regularly with 800,000 and sometimes surpass the million.
Excel has a tools to get external data form other sources. Make the file where you enter all the information as a "Data Master" with no formulas on it, jus the information. And create another file linked to the "Data Master" file with some formulas to summarize your information so you can work them more efficiently. In my case, I reduce it to 8,000 rows, and the load of information processing to the computer will get lighter. Your computer crash because the processing load is too big.
Hope this tip can help you.
Best regards.
Samuel Cruz.
I had a similar problem. But I solved it by using linked workbooks. I work regularly with 800,000 and sometimes surpass the million.
Excel has a tools to get external data form other sources. Make the file where you enter all the information as a "Data Master" with no formulas on it, jus the information. And create another file linked to the "Data Master" file with some formulas to summarize your information so you can work them more efficiently. In my case, I reduce it to 8,000 rows, and the load of information processing to the computer will get lighter. Your computer crash because the processing load is too big.
Hope this tip can help you.
Best regards.
Samuel Cruz.
NARAYANK991
Excel Ninja
Hi ,
To add to what has already been posted , you should take another look at all your formulae ; to examine your posted formula , which is :
=IF(A60044=0,0,IF(COUNTIF(A$1:A60044,A60044)=1,1,0))
this will return either 0 or 1 ; if A60044 = 0 , it will return 0 ; if A60044 is non-zero , it will return 1 if the value in A60044 is unique , and it will return 0 if it has duplicates.
You need to ask yourself whether any calculations will be done based on these formulae , and if so , what are the calculations.
Suppose we removed the 2 IF statements from the above formula , what would be the result ?
The formula itself would become :
=(COUNTIF(A$1:A60044,A60044)=1)
This formula would return TRUE if the value in A60044 has no duplicates , and FALSE otherwise.
If you reduced it even further to :
=COUNTIF(A$1:A60044,A60044)
this formula would return a number from 1 upwards ; 1 if there are no duplicates , and any other number if there are duplicates.
You need to see if you can make use of these reduced versions or not ; cell displays can always be formatted to show what ever is required , though formatting can also add to calculation times ; however , at least from the point of view of readability , the formulae themselves should be as simple as possible , as long as you are not sacrificing their functionality.
Narayan
To add to what has already been posted , you should take another look at all your formulae ; to examine your posted formula , which is :
=IF(A60044=0,0,IF(COUNTIF(A$1:A60044,A60044)=1,1,0))
this will return either 0 or 1 ; if A60044 = 0 , it will return 0 ; if A60044 is non-zero , it will return 1 if the value in A60044 is unique , and it will return 0 if it has duplicates.
You need to ask yourself whether any calculations will be done based on these formulae , and if so , what are the calculations.
Suppose we removed the 2 IF statements from the above formula , what would be the result ?
The formula itself would become :
=(COUNTIF(A$1:A60044,A60044)=1)
This formula would return TRUE if the value in A60044 has no duplicates , and FALSE otherwise.
If you reduced it even further to :
=COUNTIF(A$1:A60044,A60044)
this formula would return a number from 1 upwards ; 1 if there are no duplicates , and any other number if there are duplicates.
You need to see if you can make use of these reduced versions or not ; cell displays can always be formatted to show what ever is required , though formatting can also add to calculation times ; however , at least from the point of view of readability , the formulae themselves should be as simple as possible , as long as you are not sacrificing their functionality.
Narayan
NARAYANK991
Excel Ninja
Hi ,
I think it is difficult to say whether things will improve , but doing it right is the first step ; if everything is done right , and still your problem persists , then we can think of other approaches ; the first approach should always be to do it right.
My point was that if each and every formula is scrutinised , and improved upon , the overall effect may be significant ; you can also see if there are ways in which to use helper cells / columns / rows , so that larger and more complicated formulae are simplified.
Narayan
I think it is difficult to say whether things will improve , but doing it right is the first step ; if everything is done right , and still your problem persists , then we can think of other approaches ; the first approach should always be to do it right.
My point was that if each and every formula is scrutinised , and improved upon , the overall effect may be significant ; you can also see if there are ways in which to use helper cells / columns / rows , so that larger and more complicated formulae are simplified.
Narayan