+ Reply to Thread
Results 1 to 17 of 17

Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

  1. #1
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Hello!
    In this thread https://www.excelforum.com/excel-for...xcel-slow.html
    saw "there is little to no benefit to be had by limiting the range passed to COUNTIF" and get interested
    I checked up the above functions. The worst result is SUMPRODUCT, the rest functions seems to ignore blank cells.
    If results are correct then user defined ranges are not so actual in some cases.
    no index COUNIF
    1 COUNTIFS
    2 SUMPRODUCT
    3 SUMIFS
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    This doesn't really surprise me, I suppose, as SUMIFS and COUNFIFS ought to be better, being newer functions than SUMPRODUCT. We only recommend SUMPRODUCT where someone does not have a newer version of Excel or the calculation cannot be done with SUMIFS or COUNTIFS.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    COUNTIF is as old as SUMPRODUCT

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    SUMPRODUCT is not the bottleneck there, it is the = operator in $A:$A=$A1 that has to use every row.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Quote Originally Posted by tim201110 View Post
    COUNTIF is as old as SUMPRODUCT
    Please read what I wrote.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    SUMPRODUCT() - Ex2000
    SUMIF() - Ex2000
    SUMIFS() - Ex2007
    COUNTIF() - Ex2000
    COUNTIFS() - Ex2007

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Yes, Sandy - exactly so.

  8. #8
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    IMHO there is no use of SUMPRODUCT without operators in most cases
    even without them there is a difference

    PS SUMIF doesn't depent on range. ">="& in SUMIFS - the same
    Attached Files Attached Files

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Quote Originally Posted by sandy666 View Post
    SUMPRODUCT() - Ex2000
    SUMIF() - Ex2000
    SUMIFS() - Ex2007
    COUNTIF() - Ex2000
    COUNTIFS() - Ex2007
    To the best of my recollection, SUMPRODUCT, SUMIF and COUNTIF were available as far back as Excel 5/95 and possibly earlier versions too.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    in Ex4.0 also, but who still uses the Methuselah version?

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Quote Originally Posted by tim201110 View Post
    IMHO there is no use of SUMPRODUCT without operators in most cases
    I suspect that depends on what you do. I rarely use Sp for counting but use it frequently for weighted calculations so use it almost exclusively without operators.

    even without them there is a difference
    That is interesting. It does seem that there isn't any short-circuiting there, although the full column SP version is still considerably faster than an equivalent SUM(D:D*E:E) formula.
    Last edited by xlnitwit; 07-03-2017 at 08:53 AM.

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    'Age' has no relation to speed (at least as far as Excel functions are concerned).



  13. #13
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    "although the full column SP version is still considerably faster than an equivalent SUM(D:D*E:E) formula"
    60 sec for SUM vs 48 sec for SP. I don't think so
    Attached Files Attached Files

  14. #14
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    this is considerable, column N, PROB
    Attached Files Attached Files

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    I suppose that depends on your definition of considerably, though it was more than double the time in my test. (37.5 vs 17)

  16. #16
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Quote Originally Posted by xlnitwit View Post
    I suppose that depends on your definition of considerably, though it was more than double the time in my test. (37.5 vs 17)
    Timer is the same?
    Good PC, or smth is wrong with mine.

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Limiting the range in COUNTIF(S), SUMPRODUCT and SUMIFS

    Yes, I used your workbook but added a new column and a third iteration of the timing code. I am also using Excel 2010 rather than 2013, which could be a factor.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 12-17-2015, 11:22 AM
  2. Optimize SUMPRODUCT with dynamic sum range. SUMIFS??
    By ron2k_1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2015, 05:33 PM
  3. [SOLVED] COUNTIF/SUMPRODUCT range problems
    By L plates in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-22-2014, 03:50 PM
  4. Replies: 3
    Last Post: 06-19-2014, 10:30 AM
  5. [SOLVED] SUMIFS or SUMPRODUCT or Other solution to sum a range.
    By timgmurphy99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-30-2013, 06:57 PM
  6. SumProduct or Countif in a Date Range
    By rskinner1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-17-2013, 09:06 PM
  7. Adding =countif to an =sumproduct range (Maybe)
    By jayclinton in forum Excel General
    Replies: 4
    Last Post: 10-19-2011, 08:52 PM

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