across sheet countif function extremely slow in excel 2003

Topic

New Reply

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? 🙂

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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

38623-countif

Reply | Quote