+ Reply to Thread
Results 1 to 19 of 19

DSUM with dynamic criteria not updating in Excel 2010

  1. 08-16-2013, 10:32 AM #1
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    DSUM with dynamic criteria not updating in Excel 2010

    Good Day.

    I'm new to the forum so hopefully someone can help me out.

    I have an excel file with DSUM dynamic criteria i.e. using formulas. The strange thing is that it's not working/updating in excel 2010 at work, however, I took home the same file where I have excel 2007 and the DSUM with the dynamic criteria worked. I've attached the excel workbook. It's in the "New Method" tab. DSUM formulas are in M4 to M8.

    I'm at work now and I'm not sure if there's a particular setting that's different between excel 2010 and 2007.

    Appreciate any help that I can get. It has me stomped for a couple of hours trying to figure it out.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-16-2013, 10:47 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: DSUM with dynamic criteria not updating in Excel 2010

    I don't have 2010 currently available, but you could check that you have your Autocalcs set to on... go to Formulas tab, then Calculation Options drop down, and make sure Automatic is set on.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 08-16-2013, 02:34 PM #3
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    Thansk for you reply NBVC, "Automatic" is set on.
    Reply With QuoteRegister To Reply
  4. 08-16-2013, 02:53 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: DSUM with dynamic criteria not updating in Excel 2010

    It seems to change when prompted in Excel 2013, maybe someone with 2010 can test.... or it will have to wait for me to go home later to test on my 2010.
    Reply With QuoteRegister To Reply
  5. 08-17-2013, 07:13 AM #5
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    NBVC I'm just checking if you've had any luck with on excel 2010.
    Reply With QuoteRegister To Reply
  6. 08-17-2013, 09:01 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: DSUM with dynamic criteria not updating in Excel 2010

    Hi,

    What exactly are your expected results in M4:M8 with nothing changed in the attached?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  7. 08-17-2013, 09:54 AM #7
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    "New Method" tab M4:M8 should show similar results to "Old Method" tab M4:M8
    Reply With QuoteRegister To Reply
  8. 08-17-2013, 10:35 AM #8
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: DSUM with dynamic criteria not updating in Excel 2010

    And is the fact that the two Named Ranges - Criteria and PizzaData - currently produce #REF! errors an issue?

    Regards
    Reply With QuoteRegister To Reply
  9. 08-17-2013, 10:46 AM #9
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    No as they are not used.
    Reply With QuoteRegister To Reply
  10. 08-17-2013, 10:47 AM #10
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    The issue is that it works in excel 2007 and not in excel 2010.
    Reply With QuoteRegister To Reply
  11. 08-17-2013, 10:49 AM #11
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: DSUM with dynamic criteria not updating in Excel 2010

    have you considered using sumifS() for what you need?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  12. 08-17-2013, 10:57 AM #12
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: DSUM with dynamic criteria not updating in Excel 2010

    I appreciate that, but I am just trying to gather as much information as I can so as to try to answer your question.

    Can you tell me if the only entries which are different when you open it in 2007 from those when you open it in 2010 are those in cells M4:M8?

    I have to confess that I'm not much of an expert in the D-functions. Having said that, I know enough to see why your 'Old Method' works, though I'm struggling to understand how your 'Org New Method' could work, in whatever version of Excel, since - as I understood it - the criteria field for DSUM has to include a column label in its first row. This is the case in your 'Old Method' sheet, though not in the 'Org New Method' sheet, in which the first row of the criteria field you have entered ($I$4:$J$5) is blank.

    Regards
    Reply With QuoteRegister To Reply
  13. 08-17-2013, 02:20 PM #13
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    FDibbins: I may have to go that route. Hopefully there will not be any performance degradation as it will be applied to fairly large data set.

    XOR LX: Yes the entries in cells M4:M8 match 'Old Method' sheet when opened in 2007. Also when a formula is in the criteria, the column heading should be blank or should not match any headings in the data/database.
    Reply With QuoteRegister To Reply
  14. 08-17-2013, 02:35 PM #14
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: DSUM with dynamic criteria not updating in Excel 2010

    "Also when a formula is in the criteria, the column heading should be blank or should not match any headings in the data/database"

    You do not have a formula in the criteria: you have simply a range ($I$4:$J$5).

    Regards
    Reply With QuoteRegister To Reply
  15. 08-17-2013, 02:44 PM #15
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    Apologies. I should have said when a formula is in a cell in the range for the criteria.
    Reply With QuoteRegister To Reply
  16. 08-17-2013, 02:46 PM #16
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: DSUM with dynamic criteria not updating in Excel 2010

    Ah, I see. Interesting. I need some more time to investigate this. Or hopefully someone with a little more knowledge of D-functions will get there before I do.

    Regards
    Reply With QuoteRegister To Reply
  17. 08-17-2013, 04:01 PM #17
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: DSUM with dynamic criteria not updating in Excel 2010

    FDibbins: I may have to go that route. Hopefully there will not be any performance degradation as it will be applied to fairly large data set.
    Im pretty sure sumifs() will be quicker that dsum() - as well as a heck of a lot simpler
    Reply With QuoteRegister To Reply
  18. 08-17-2013, 06:23 PM #18
    JosephP
    JosephP is offline
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: DSUM with dynamic criteria not updating in Excel 2010

    I believe that is a bug in 2007-it doesn't work in 2003, 2010 or 2013 for me and I don't believe it should
    Josie

    if at first you don't succeed try doing it the way your wife told you to
    Reply With QuoteRegister To Reply
  19. 08-20-2013, 10:23 AM #19
    imassiah
    imassiah is offline
    Registered User
    Join Date
    08-15-2013
    Location
    St. John's, Antigua
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: DSUM with dynamic criteria not updating in Excel 2010

    Thanks all. I'll just have to use Sumifs.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. DSUM with dynamic criteria?
    By yellephant in forum Excel General
    Replies: 13
    Last Post: 01-05-2019, 01:37 PM
  2. DGET, DMAX, etc. no longer accept criteria with dynamic references 2007 vs 2010?
    By NLawrence in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2013, 03:44 AM
  3. Excel 2008 : DSUM or SUBTOTAL with Dynamic Criteria from a Large List
    By friedland_industries in forum Excel General
    Replies: 7
    Last Post: 07-27-2011, 03:08 PM
  4. [SOLVED] Problem with criteria for DSUM (Excel XP)
    By Mike Webb in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-01-2006, 05:30 PM
  5. [SOLVED] Excel: Using DSUM, I need to evaluate 2 criteria that are not toge
    By Know enough to get myself in too deep in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2006, 05:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1