COUNTIF calculates slow (10000+ records)
 

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: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?
--
br, Vidar

COUNTIF calculates slow (10000+ records)
 

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).


"VidarHe" wrote:

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: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?
--
br, Vidar

COUNTIF calculates slow (10000+ records)
 

Hello Vidar,

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

Regards,
Bernd

COUNTIF calculates slow (10000+ records)
 

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.
--
br
Vidar


"JLatham" wrote:

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).


"VidarHe" wrote:

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: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?
--
br, Vidar

COUNTIF calculates slow (10000+ records)
 

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?

--
br
Vidar


"Bernd P" wrote:

Hello Vidar,

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

Regards,
Bernd