slooooow calculation with COUNTIF

[loor]

Need some advise from someone who has the black belt in excel.


Im preparing the main data for subsequent pivottabeling, but my data suffers from slow calculation due to the use of a series of COUNTIFS.


The COUNTIF is looking in a cell where week numbers have been set up in a string (dataimport) seperated by commas. In order to calculate across a number of rooms I have set up 52 cells representing the weeks of the year and then use COUNTIF to check if the weeknumber is occurs in the string.


Column J "Scheduled weeks" value example: ,6,8,10,15,33,34,35,52,

Column T "week1", Formula: =COUNTIF($J2;"*,"&T$1&",*")


Since Im counting the number of bookings over a year I have 7200 rows counting on 52 weeks - which kinda' takes 5 min to calculate everytime a cell is changed, or sheet opens/saves.


Any takes on this? Also is it possible to add a sample/picture in this blog?

 

[Hui]

Loor,

1st Posting of Files or Pictures isn't enabled here at Chandoo.org

Have a read of http://chandoo.org/forums/topic/posting-a-sample-workbook


For your question:


First step is don't maintain calculations for the sake of maintaining calculations, ie: Do a calculation like your Countif and then convert the answers to values


If your doing multiple Rows or Columns break the calculations into blocks or Rows or Columns and do the calc and then copy/paste as values


You can always save an example of your formulas on another sheet for next time or write a macro to redeploy them if required.


It may be possible to do something else to speed it up but without the real data it is difficult to advise.

 

[loor]

Thank you HUI


You're so right! The data is static so no need to hold on to anything but the values and as you say I will keep the formula in the first or last of the rows - for next time I need to recalculate a new batch.


Brilliant, brilliant.


/loor