inconsistent results with SUMPRODUCT

[moneypennie21]

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

[AliGW]

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.

[XLent]

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

Please Login or Register  to view this content.

[moneypennie21]

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

[Richard Buttrey]

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

[XLent]

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.

... 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?

[Richard Buttrey]

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.

[XLent]

Closed workbook.

[moneypennie21]

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