COUNTIF and SUMIF slow recalculation (Excel 2003)

[StuartR]

I have a workbook that used to recalculate in 1 or 2 seconds under earlier versions of Excel and takes over 15 seconds in Excel 2003. This is a long time and I get lots of user complaints for which the only suggestion I have is to turn off automatic recalculation.

I tried deleting and simplifying formulae until I identified the slow cells, which is an array of about 500 cells which do lots of COUNTIF and SUMIF calculations on arrays on other sheets. If I delete any half of these formulae then the recalculate time halves and if I delete all of them then it reverts to 1 or 2 seconds.

Unfortunately I can't see any easy way to eliminate them. I really do need to know how many of each of about 20 different values occur in an array of about 70 cells in each of 25 worksheets.

Any suggestions about how to speed up COUNTIF and SUMIF in Excel 2003?

StuartR

[HansV]

I don't have Excel 2003, so I cannot suggest anything from experience, but you might try using SUM or SUMPRODUCT instead of COUNTIF and SUMIF. You'll have to test yourself if they are faster.

Say that you want to count cells in D446 that contain "M". The COUNTIF formula is

=COUNTIF(D446,"M")

Alternative formulas are:

=SUM(N(D446="M")) as an array formula (confirm with Ctrl+Shift+Enter)
=SUM(1*(D446="M")) as an array formula (confirm with Ctrl+Shift+Enter)
=SUMPRODUCT(N(D446="M")) as a standard formula
=SUMPRODUCT(1*(D446="M")) as a standard formula

If you want to add cells in C4:C46 where the corresponding cell in D446 contains "M". The SUMIF formula is

=SUMIF(D446,"M",C4:C46)

Alternative formulas are:

=SUM(C4:C46*(D446="M")) as an array formula (confirm with Ctrl+Shift+Enter)
=SUMPRODUCT(C4:C46,N(D446="M")) as a standard formula
=SUMPRODUCT(C4:C46,1*(D446="M")) as a standard formula
=SUMPRODUCT(C4:C46*(D446="M")) as a standard formula

Note: the 1* and the N function force TRUE and FALSE to be interpreted as 1 and 0.

[StuartR]

Thanks Hans,

I'll give some of these a try. It will take a while because I'll have to edit hundreds of cells and I have other work to do first. I'll let you know how it works out.

StuartR

[StuartR]

Your examples set me thinking and I have now come up with a simple solution to my problem.

My formulae were all acting on entire columns in the target worksheets. On earlier versions of Excel this worked fine, I assume that it only actually looked at the cells in the Used Range on the target sheet, I guess Excel 2003 must be looking at every cell in the column!

If I restrict the target range of the COUNTIF and SUMIF to just a few hundred rows then the performance is fine!. This is a bit tricky since I want it to operate on the entire used range on each of 25 or so target sheets, but I rarely use more than 100 rows so setting ithe formulae to use 1000 rows is pretty safe.

StuartR

[Aladin Akyurek]

Would you care to post those SumIf and Countif formulas you were using?

[StuartR]

I don't know how much they will help without the rest of the (confidential) workbook. Here are some examples (sheet and cell names changed to protect confidentiality)
<code>
=COUNTIF('Sheet Name'!AE:AE,txtCellName)
=COUNTIF('Sheet Name'!$AH:$AH,txtCellName&"_"&OFFSET(rngRangeName, 0,4,1,1))
</code>
StuartR

[AndrewO]

Stuart
Blast & <img src=/w3timages/censored.gif alt=censored border=0> - column selection was such a resilient trick
I sometimes find it handy to cover variable ranges with a Header and Footer row (somewhat depends on sheet design)
Then its easy to specify a range as Offset(Header,+1,0):Offset(Footer,-1,0) in formulae and users tend not to make errors when inserting rows

Alternatively, a rangename could be redefined as part of an event routine on open - but I'm sure your already knew that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Andrew

[StuartR]

Thanks for the input.

I already have code in a SheetChange event that calculates the last row of interest in the current sheet, and it is simple enough to store this information away on a hidden column so I can use it in my summary sheet (and the user can't even change it by accident because the SheetChange event immediately fires and puts the correct value back in the cell).

It was very easy to do a global replace in my summary sheet - replacing AE:AE with AE1:AE200 etc, but to do this properly I now need to replace a really simple formula like
=COUNTIF('Sheet Name'!AE1:AE200,txtCellName)
with
=COUNTIF(OFFSET('Sheet Name'!AE1,0,0,'Sheet Name'!A5,1),txtCellName)

This needs to include the sheet name in the replacement, so my 5 or 6 global replaces now turn into 150 error prone attempts.

So it goes.

StuartR

[AndrewO]

SO long as you don't have lots of different columns to work on wouldn't it be easier for your sheet event to simply define range names?

e.g Sheetn_AE equals Sheetn!AE1:AE2000 and so on

then the Countif would be clean.

[StuartR]

I thought of doing it that way, but I've tried to avoid sheet specific named ranges, as I have a large number of similar sheets and we frequently add/delete/rename them.

I did the mass edit this afternoon. It only took 92 Edit / Replace commands, and I discovered that I actually had 1492 of these cells to modify.

StuartR

[Aladin Akyurek]

Try...

In A1 (or in some other convenient cell) enter:

=MATCH(REPT("z",255),'Sheet Name'!AE:AE)

=COUNTIF('Sheet Name'!$AE$2:INDEX('Sheet Name'!$AE:$AE,$A$1),txtCellName)

In A2 (or in some other convenient cell) enter:

=MATCH(REPT("z",255),'Sheet Name'!AH:AH)

=COUNTIF('Sheet Name'!$AH$2:INDEX('Sheet Name'!$AH:$AH,$A$2),txtCellName&"_"&INDEX(rngRange Name,1,5))

If you still have an unbearable response time, revert to the original formulas with whole column references where you only replace the OFFSET() bit in the second formula with the appropriate INDEX() bit.

[StuartR]

Aladin,

Can you please provide an annotated explanation of your suggestion - my skills are much more in the Word and Windows arena than in Excel and this is all starting to look like smoke and mirrors to me.

StuartR
edited by StuartR to add
I've now played with your formulae and I can see how they work. Very cute. Thank you.
What a lot of work because Excel 2003 thinks it needs to count thousands of cells it knows to be empty!

[StuartR]

You may be amused to learn that this code was fine through all my testing, I shipped it a couple of weeks ago - and immediately learned about the silly assumption!

My chinese colleagues had to disabuse me of the mistaken impression that Z would always be the last letter of the alphabet.

Back to the drawing board.

StuartR

[StuartR]

Talking to myself, again...

A bit of trial and error shows that MATCH("text",range,1) returns #NA if there is any chinese text in the range. But MATCH("text", range, 0) is fine. I guess it can find exact matches but it can't decide which is later/earlier in sequence. This was enough for me to devise a workaround, but I thought others might be interested.

StuartR

[Aladin Akyurek]

It looks like the MATCH idiom misses some Chinese text that is in the last used cell. That's a bit strange though if they ASCII-based software.

The following might help:

=IF(ISBLANK(A65536),MATCH(2,1/(1-ISBLANK(A1:A65535))),65536)

which needs to be confirmed with control+shift+enter instead of just with enter.

The foregoing is equivalent to and less efficient than:

=MATCH(REPT("z",255),A:A)