Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    COUNTIF and SUMIF slow recalculation (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 2003)

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 2003)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    Would you care to post those SumIf and Countif formulas you were using?
    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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

  7. #7
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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

  11. #11
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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.
    Microsoft MVP - Excel

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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!

  13. #13
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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

  14. #14
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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

  15. #15
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF and SUMIF slow recalculation (Excel 20

    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)
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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