+ Reply to Thread
Results 1 to 14 of 14

SUMIFS producing #VALUE!

  1. #1
    Registered User
    Join Date
    11-23-2013
    Location
    La Crosse, Wi
    MS-Off Ver
    Excel 2010
    Posts
    62

    SUMIFS producing #VALUE!

    I am trying to calculate the number of deaths from tornadoes on various days. The problem is that the death toll is not always known, so I am using ? for that day. For days which are all ? marks, I would prefer to get just a blank cell or simply (missing) if at all possible. If there are mixed values of ? marks and values, I would prefer to just count all of the deaths on that day and come back with a total death toll. I am using the following equation.

    =SUMIFS($D$2:$D$38,$I2,$E$2:$E$38)

    To further complicate things, some of my dates are in the 1800s. It should not be an issue because I am using the same date though.

    I have attached a sample of my spreadsheet.

    Thanks in advance for any help you can provide.

    Jeff B
    Attached Files Attached Files
    Last edited by AliGW; 07-29-2020 at 04:10 AM. Reason: Solved tag added - no need to edit thread title or add solved to post. Thanks.

  2. #2
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,096

    Re: SUMIFS producing #VALUE!

    Should be

    =SUMIF($D$2:$D$38,$I2,$E$2:$E$38)

    or
    =SUMIFS($E$2:$E$38,$D$2:$D$38,$I2)

  3. #3
    Registered User
    Join Date
    11-23-2013
    Location
    La Crosse, Wi
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: SUMIFS producing #VALUE!

    Thanks for the help!

    Jeff

  4. #4
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,026

    Re: Solved: SUMIFS producing #VALUE!

    or even =SUMIF($D:$D,$I2,$E:$E) . For SUMIFS whole column could be used also but as a rule the performance is worse in this case because sumifs doesn't use used range. May be MS fix it in the future.

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,457

    Re: Solved: SUMIFS producing #VALUE!

    Quote Originally Posted by BMV View Post
    because sumifs doesn't use used range
    Is there a link to sum official documentation you can provide to support this? Or is it a result of some tests you yourself have carried out?

    Regards
    Click * below if this answer helped

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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,036

    Re: SUMIFS producing #VALUE!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    I have done it for you this time - there is no need to edit the thread title.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,026

    Re: Solved: SUMIFS producing #VALUE!

    @XOR LX . Yes it's result of tests but now i can't reproduce it . It's looks likу it was fixed. I get differences only for countifs function and only for several condition
    For 5000 calculation
    =COUNTIFS(A:A;1;B:B;2) 18,80469 s
    =COUNTIFS($A$1:$A$200001;1;$B$1:$B$200001;2) 14,54297 s

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,884

    Re: SUMIFS producing #VALUE!

    Hi BMV, the issue you outlined applies only to the COUNT function, not the SUM.

    the attached is taken from an old thread but something I use to highlight the issue when it comes up for discussion - as this isn't documented.

    The 5k COUNTIFS will calc in ~17s (on my machine) whereas SUMIFS will take 0.1s
    (sometimes, if being used in large volume, I will advise people to add the 1 field so they can SUMIFS in preference to COUNTIFS)

    FWIW, @XOR LX corrected me to the same effect as I saw the issue with COUNTIFS and presumed it applied to all - but it does not (thankfully!)
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,026

    Re: SUMIFS producing #VALUE!

    @XLent Hi. it could be my mistake (sum -count)

  10. #10
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,457

    Re: SUMIFS producing #VALUE!

    Thanks, XLent, BMV.

    Now we just need to understand why there is such a glaring discrepancy with respect to calculation time for these two functions of the same 'family'! Why would MS design it such that one operates over the used range only but not the other?

    Cheers

  11. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,457

    Re: SUMIFS producing #VALUE!

    @XLent

    Apologies for my VBA ignorance, but could you shed some light on why the calculation times given by your procedure differ from those obtained by performing a full manual workbook recalculation?

    Perhaps it's related to this, as given at decisionmodels.com:

    "[In Manual Calculation Mode, VBA Range.Calculate] calculates all the cells in the selected range left-to-right and top-to-bottom. Excel 97 and Excel 2000 ignore dependencies within the selected range, but Excel 2002 and Excel 2003 follow the left-to-right and top-to-bottom calculation with a dependency recalculation of the selected range only.
    This means that range.calculate can be significantly slower in Excel 2002 and 2003 than in Excel 97 and 2000, and will give different results if there are dependencies within the range being calculated that are not left-to-right and top-to-bottom.
    "

    Presume that newer versions of Excel operate similarly to Excel 2003 in this respect, as a full manual recalculation of the COUNTIFS formula range takes significantly less than that obtained using the BasicTimer sub.

    Of course, this does not in any way detract from the main point you're making, i.e. that SUMIFS is far more efficient than COUNTIFS.

    Regards

  12. #12
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,884

    Re: SUMIFS producing #VALUE!

    Interesting.... and, I honestly don't know but, reading one of Charles Williams' white papers (he of Decision Models):

    Range.CalculateRowMajorOrder calculates the range left to right and top to bottom, ignoring all dependencies.
    Range.Calculate calculates the range resolving all dependencies within the range.


    implying that, were this the cause, changing to the former method should result in a big performance gain -- but I didn't experience that (very similar)

    I might create a new thread on this, fear we hijack the thread and incur the wrath of a Mod (or two...)

  13. #13
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,026

    Re: SUMIFS producing #VALUE!

    Offtop
    XOR LX, XLent could you test vlookup function also. I have info about catastrophic dependency from range in the new version of Excel. Unfortunately there is article in Russian but
    Способ 1. ВПР is VLOOKUP(B2,$G$2:$H$600,2,0) 4,3 s
    Способ 2. ВПР с выделением столбцов целиком VLOOKUP(B2,$G:$H,2,0) -14,5 s

    Dear moderator if it is not possible to discuss here sorry. I agree with XLent it could be new thread.
    Last edited by BMV; 07-30-2020 at 04:09 AM.

  14. #14
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,457

    Re: SUMIFS producing #VALUE!

    @BMV Interesting - I'll get back to you on that one.

    Re SUMIFS vs COUNTIFS, I have discovered the following:

    First, note that COUNTIFS performs as well as SUMIFS when there is only one criteria_range (and by definition COUNTIF performs equally well). The large differences in performance occur when there is more than one criteria_range.

    I would hypothesize that the sum_range is always used to determine the used range for SUMIFS.

    For COUNTIFS, I assume that, if there is only one criteria_range, then that is used to determine the used range. When there is more than one criteria_range, it is not clear what algorithm is being employed to determine the used range. However, we can be sure that one is being employed: you only have to compare COUNTIFS to SUMPRODUCT using entire column references to see that, as bad as it is compared to SUMIFS, COUNTIFS is nevertheless clearly not processing all 1,048,576 rows as SUMPRODUCT has to.

    Also, there does not appear to be any difference in performance for COUNTIFS with 2, 3, 4, ..., i.e. an arbitrarily large number of criteria_ranges: they all perform equally badly compared to the version with a single criteria_range.

    What we need to understand is how COUNTIFS is determining the used range in the multi-criteria_range case.

    Regards

+ 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: 0
    Last Post: 08-22-2017, 03:49 PM
  2. [SOLVED] ABS and If producing !VALUE!
    By markDuffy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2013, 06:39 PM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  5. [SOLVED] sumproduct producing #VALUE!
    By Aladin Akyurek in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 09-06-2005, 06:05 PM
  6. sumproduct producing #VALUE!
    By Aladin Akyurek in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 11:05 AM
  7. [SOLVED] UDF producing #NAME?
    By Jim May in forum Excel General
    Replies: 4
    Last Post: 04-23-2005, 01:06 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