COUNTIF calculates slow (10000+ records)

[VidarHe]

Hi,
We have a printer repair log where we count the number of times the printer
has been in based on the serial number. But we have 1000+ log entries and
every time we change a serial number or adds a new it calculates a long time
all the way. Can this be speeded up or as an alternative only calculated the
first time?

A B C D
1 SerialScan Count Serial#
2 12345 4 12345
3 12 345 4 12345
4 12-345 4 12345
5 12-346 1 12346
6 12-347 1 12347

This is the formula in the Count column copied all the way down
=IF(D3="";"";COUNTIF(D;D3))
Does not count if no serial (D3="") so at least empty rows do net get counted.

The serial# is derived from SerialScan wich can contain serial with minus
and spaces but stipped for counting purposes
=(SUBSTITUTE(SUBSTITUTE(B3;" ";"");"-";""))

Any tips?

 

[JLatham]

Getting rid of the SUBSTITUTE() formulas in column D may help some - although
you may want to leave one or 2 of them near the end of the existing list as a
reminder/seed for added entries later:

Select the cells in column D that you want to convert to their current
displayed value and use Edit --> Copy. Without taking any further action
(leave those cells selected) use Edit --> Paste Special with the "Values"
option selected. That will remove the formula, replacing it with what's
displayed in the cell. That should reduce the recalculation load some. Keep
in mind that after doing this, if you sort the data, you need to include all
columns in the sort (column D will no longer rebuild those cell contents from
column A entries).

 

[Bernd P]

Hello Vidar,

I suggest to take my UDF Lfreq or Pfreq:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[VidarHe]

Hi
Tried to Remove the SUBSTITUTE formula in column D (serial#) but this have
no effect. It is the COUNTIF formula on 1000 records counting in 10000 serial
numbers that takes up alle the calculating time. But we have removed the
COUNTIF formula on previous years on the C column (count). This have an
impact now but then we can only see how many times the printer was repaired
up to this date. But better than nothing.

 

[VidarHe]

Was trying to avoid macros with the new xlsm and trusting ... We will keep
the workaround with removing the formulas on records from previous years and
only keep valus there. But then another workaround could be to just run
COUNTIF the first time the record was entered. The most pressing is to get
notified that the printer has been in fore and then we can manually search
for the occurences/filter. Any ideas on how to do this?

 

[ytayta555]

Can this be speeded up or as an alternative ... ?

THE first improvement in speed , of 33 % , is to modify formula
in C column ( count ) ;
I put serial numbers in 15000 rows in column B (SerialScan) ,
15000 formulas in column D,( Scan#) , and 2001 formulas in C column,
(count) :
In my sistem , CPU 1,46 Ghz , 256 Ram , your formula :
=IF(D2="","",COUNTIF(D;D2)) take about 30 seconds ;
changed Countif to work in a smaler range :
=IF(D2="","",COUNTIF(D$2$11001,D2)) , time for calculate is
20 seconds , 33 % improvement , but still too slow . I'm shure there
still can be another improvements .

 

[ytayta555]

Another three suggestions are :

Was trying to avoid macros with the new xlsm and trusting

1) If your CPU is dual core , you can set Excel 2007 to
calculate with both cores , see Controlling Calculation Options :
http://msdn.microsoft.com/en-us/library/aa730921.aspx

2)CountIf function work faster if the criteria is built in it , and
CountIf
function don't must to find the criteria in another cell :
=IF(D2="","",COUNTIF(D$2$11001, 123454)) instead of
=IF(D2="","",COUNTIF(D$2$11001, D2)), but I don't know
very well your task and mode of editing in Column B .

3)You can insert an Event Macro , to set
Application.Calculation = xlCalculationManual ,
and , maybe , Application.ScreenUpdating = False ,
whenever you work in Column B ( change a serial number or
adds a new , in Column B ) ; then , when you clik or select
something out of Column B , to come back to
Application.Calculation = xlCalculationAutomatic
and Application.ScreenUpdating = True .