+ Reply to Thread
Results 1 to 9 of 9

inconsistent results with SUMPRODUCT

  1. #1
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    65

    inconsistent results with SUMPRODUCT

    Hi,

    I am using sumproduct across two different workbooks - (1 will be closed when the SUMPRODUCT one is open) I am using it as a nested SUMIF so I am adding the results in column AF based on names in Column F - this is the formula

    =SUMPRODUCT((--('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$F$7:$F$8000="Dog")*('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$AF$7:$AF$8000)))+SUMPRODUCT((--('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$F$7:$F$8000="Cat")*('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$AF$7:$AF$8000)))+SUMPRODUCT((--('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$F$7:$F$8000="Hamster")*('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$AF$7:$AF$8000)))+SUMPRODUCT((--('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$F$7:$F$8000="Rabbit")*('[DLS Error Report 2018 MASTER.xlsx]Error Summary By Branch'!$AF$7:$AF$8000)))

    the formula works as expected except when the DLS report is closed - it still works but gives a slightly different result - the same formula summing a different column works fine whether the DLS report is open or closed.

    I can't work out why this is, any ideas?

    Thanks

    Jen

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,944

    Re: inconsistent results with SUMPRODUCT

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    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.

  3. #3
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    976

    Re: inconsistent results with SUMPRODUCT

    on an aside, you should find you can merge those four separate SUMPRODUCTs into one single call:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    65

    Re: inconsistent results with SUMPRODUCT

    Hi,

    AliGW - I tried to replicate this in a spreadsheet I could upload and I couldn't.

    XLent - I have made the suggested changes to the formula and this is really useful to know, it hasn't solved this original problem. basically when I have both workbooks open the results are correct but when I only open the one with the formulas in the results are different by a couple of 100. I think I know the cause of it but I don't know how to fix it - the DLS report that the formulas are taking the data from is one that is created using formulas and when you open it it asks if you want it to update, when you click yes this is when the numbers in the SUMPRODUCT result change as the report updates.

    unfortunately the report is DLS report is read only so unless there is another way around this I will; have to ask the BI team to save it up to date.

    Thanks

    Jen

    Jen

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,538

    Re: inconsistent results with SUMPRODUCT

    I presume that's a typo where you mention
    Error Summary By Branch'!$F$7:$F$8000=
    in various places and you meant
    Error Summary By Branch'!$AF$7:$F$8000=

    However as Ali has said, upload the workbook
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    976

    Re: inconsistent results with SUMPRODUCT

    Quote Originally Posted by moneypennie21
    XLent - I have made the suggested changes to the formula and this is really useful to know, it hasn't solved this original problem.
    agreed, though, to be clear, that wasn't the intent... rather performance, i.e. the marginal increase in overhead c/o additional MATCH is significantly less than the overhead of 3 additional SUMPRODUCTs.

    Quote Originally Posted by moneypennie21
    ... basically when I have both workbooks open the results are correct but when I only open the one with the formulas in the results are different by a couple of 100. I think I know the cause of it but I don't know how to fix it - the DLS report that the formulas are taking the data from is one that is created using formulas and when you open it it asks if you want it to update, when you click yes this is when the numbers in the SUMPRODUCT result change as the report updates.
    I confess I try to avoid external links but I noted, previously, that you have no control over the DLS file so if, as you say, this itself has external dependencies then inconsistencies are perhaps unavoidable. You mention that the result is wrong unless the DLS file is opened, and recalculated, however that seems a bit "schrodinger's cat"... i.e. it probably is right until you open the DLS file and update it?

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,538

    Re: inconsistent results with SUMPRODUCT

    This Microsoft Article should explain all.

    https://support.office.com/en-us/art...3-dd357fe0e653

    Why incidentally are you using SUMPRODUCT for this? Sumproduct was the way to go before XL 2007 and was a really useful solution to many analysis problems. It came to be used in more ways than its designers ever envisaged I suspect.

    SUMIFS has been the preferred way for many since XL 2007.

  8. #8
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    976

    Re: inconsistent results with SUMPRODUCT

    Closed workbook.

  9. #9
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    65

    Re: inconsistent results with SUMPRODUCT

    Hi Richard- Column AF contains the numbers I am trying to sum and column F contains the criteria I want to look for - however I have found the problem is with the source data workbook and not my formula.

    XLent - Yes I get that you knew this wouldn't solve the problem sorry if I didn't make the clear, I totally agree your MATCH solution is much more elegant than my original multiple SUMPRODUCT formulas so I will note this in my excel bible for future reference. Now I know what the issue is I can discuss with our BI team to ensure the source report is always saved up to date

    thanks all

    Jen

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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