+ Reply to Thread
Results 1 to 10 of 10

SUMPRODUCT/COUNTIF being very slow and crashing

  1. #1
    Registered User
    Join Date
    10-05-2007
    Posts
    74

    SUMPRODUCT/COUNTIF being very slow and crashing

    I am having trouble with the SUMPRODUCT/COUNTIF Formula being very slow, and constantly crashing my PC. I was wondering if you could please point me at an alternative formula to the attached.
    =SUMPRODUCT(--(E2:E10<>"");1/COUNTIF(E2:E10;E2:E10&""))

    Blue area
    CONCATENATE range will be a minimum of 20,000 lines.

    Yellow area
    The intended result

    ExcelTip.xlsx
    Last edited by Sordini; 05-08-2012 at 05:47 AM.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    Hi,

    your formula is good, probably you could use an efficient (unfortunately not elegant) pivot table to count unique "concatenates".

    There are for sure better solutions.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 05-08-2012 at 05:27 AM. Reason: Attached not saved file
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    you could try since col e will never be blank
    =SUMPRODUCT(1/COUNTIF(E2:E10,E2:E10))-IF(COUNTIF(E2:E10,"--"),1,0)
    Last edited by martindwilson; 05-08-2012 at 05:30 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    Hi

    My opinion is that it's better to work with Pivot tables( As CANAPONE, suggestet) or if you want formulae it's better to use a helper and hidden column, than an Array formula like SUMPRODUCT.

    In H1.

    =COUNTIF(E2:$E$10,E2)

    Copy down.

    Then use this, to get your result.

    =COUNTIF(H2:H10;1)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    10-05-2007
    Posts
    74

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    Dear Canapone. That is very sneaky. And fast, which is exactly what I need. Thanks a lot.

    Thank you Martindwilson, but I am afraid that formula is equally probelmatic due to the size of the work sheet. But thanks all the same.

  6. #6
    Registered User
    Join Date
    10-05-2007
    Posts
    74

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    Thanks Fotis1991, That one was so simple, I feel really stupid now :-) But in a good way!

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    You are welcome!

    Most of the times, Simple is better..!

  8. #8
    Registered User
    Join Date
    05-08-2012
    Location
    Phillipines
    MS-Off Ver
    Excel 2007/2010
    Posts
    11

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    hello I am very new to excel and was just starting: However, I would need to sort out my excel file for my survey result tracker.

    I need that on each category, everything i put on a certain date, say, on the 18th, all result on the 18 will be automatically feed on the "daily sheet" under the corresponding dates under the specific ranges:

    say: for cat 1, on the 18th of Apr, i need excel to automatically show me the average and total numbers of result gathered on that day.Same as the the Per question daily average. They say sum product is best. I have attached here . April 18 and 19 data have been entered manually.TO EDIT22.xlsx

    I desperately need help
    Thanks so much

  9. #9
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    Hi Pash09,

    in order to have more visibility in the Forum and more answers and support, open a new thread trying to explain in the title the problem you need to solve.

    Regards

  10. #10
    Registered User
    Join Date
    05-08-2012
    Location
    Phillipines
    MS-Off Ver
    Excel 2007/2010
    Posts
    11

    Re: SUMPRODUCT/COUNTIF being very slow and crashing

    hi, sorry it seems i did it wrongly

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1