Excel Crash While Using Countif Formula

Question

Hi Experts,

while using below formula on column B of 3 lac rows, my excel crash and stop working. my data have 20 columns and almost 3 lac rows. Is there any solution to paste the formula smoothly in this file of look like this data.

Countif(A3:$A$3,A3)

Rgds.

Waseem

Answer 1

Hi Vijay

my apology for reply late,

above mentioned tips are not helpful to me. if I am use simple count formula it works fine for 3 lac rows but when I use above mentioned formula to 3 lac data, excel crash.

Rgds.

Waseem

Answer 2

you are right, my data is in column B and formula result in column C.

actually I am identifying duplicate values that how many times they repeat and mark them serial #. mean of data 42 repeat three times 1st result should be (1) 2nd result should be (2) 3rd result should be (3) so on.

my formula is working perfectly but excel crash during paste to 3 lac rows.

Rgds.

Answer 3

here are two screen shots.

Answer 4

Hi Waseem

I presume you are not using this formula =Countif(A3:$A$3,A3)

Can you please provide the exact formula you are using and also a screenshot of the data you are performing that calculation on, so I can get a better understanding of the problem . ..

Answer 5

Hi WaseemAhmad71

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

1. Sometimes, add-ins can cause problems. To determine, if add-in is a problem start your application in safe mode and see if problem has gone away.

To start in Safe Mode -

Hold CTRL key and click on application icon and don't release the CTRL key till it asks you for Safe mode confirmation.

If yes, then start application normally and disable add-ins one by one and start application again every time you disable an add-in to determine the culprit add-in.

2. Open Excel > File > Options > Advanced > Go to Display section > Check hardware graphics acceleration
3. Open Excel > File > Options > Advanced > Go to General section > Check Ignore other applications that use Dynamic Data Exchange (DDE)
4. Sometimes, this is caused due to application not being able to access the default printer. Close all office applications and change your default printer through Start > Devices and Printers to another printer. It may be some physical one or One Note or XPS or PDF or anything else. If this is the problem, then you will need to update your printer driver for default printer.

Do let me know if you require any further help on this. Will be glad to help you.