Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    across sheet countif function extremely slow in excel 2003

    I used to write my spreadsheet with excel 2002, but recently I start to use excel 2003 and it mess up so many of my workbooks. after some testing I found out that countif function is the problem which make calculation slow.

    It wasn't a problem in previous version of excel, but it become a problem in 2003, especially across sheet countif is going to take a minute to calculate just hundreds of data.

    does anyone had experienced this problem before? any suggestions?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    The problem may be more of the way you are using it. Do you have a Countif function in a formula in one cell that counts the criteria for the entire column or are you using the Countif function in every cell in that column?

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    The problem may be more of the way you are using it. Do you have a Countif function in a formula in one cell that counts the criteria for the entire column or are you using the Countif function in every cell in that column?
    can you kind explain the difference between these two cases? thanks

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Sure Stanley,

    Not saying that you have your worksheet setup like this but here is an example of an inefficient use of the Countif formula. A vendor wants to track the sales of his 5 widgets on a daily basis over a year’s time. In column A are the dates starting January 1, 2014 to December 31, 2014 spanned by a list of all the sales in the order they were processed (Column B) for that day. The full year is spanned by the number of totals sales yielding 5450 rows. Column C-G are the columns for tracking each of the widgets. Starting in cell C2 (under widget 1) the formula =COUNTIF($B$2:$B2,"widget 1") is entered and copied down to row 5450. The same formula for widget 2 is copied down and so on. Each column shows a running total of that widgets sold at the expense of over 25,000 Countif formulas. A change in the sale a widget takes about 2 second on my computer to recalculate the widgets column. See the image below.

    Maud

    countif.png

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •