Sumproduct returns 0

[Hawkeyed]

Hi all,

I'm running into an issue with the SUMPRODUCT formula returning 0 instead of the actual values.

=SUMPRODUCT(SUMIF(INDIRECT("'"&listsheets&"'!C2:C358");$A$2;INDIRECT("'"&listsheets&"'!D2:D358")))

"'"&listsheets&"'!C2:C358" - here is where I am looking for an item code
$A$2 - here is the input of the item code
"'"&listsheets&"'!D2:D358" - here is where the quantity (value) of the item should be returned

It worked with the first few worksheets but when I add more (and there is a lot of them) formula starts to returns 0's instead.

 

[jasonb75]

That should only happen if the item is not found in any of the listed sheets.

A few things to check:-

Is calculation set to automatic? Manual calculation would not give you a result when you enter the formula, you would need to force calculate them.

Is the sheet with the formula included in the list? If the formula is trying to sum itself then it will create a circular reference that could be the cause of the problem, although you should see a warning message if this is the case.

Are the codes and values in the same ranges on every sheet? Relative references don't change when you drag INDIRECT formulas.

 

[Hawkeyed]

How I can check if it is set to automatic or not?

Is the sheet with the formula included in the list?

I am not sure I understand the question. Did you mean this part? listsheets. If yes, then I have created listsheets in Name Manager. Everything worked fine in the first few sheets. The formula was calculating everything how it was supposed to. I have checked every single item code.
I am not getting warning messages. It just returns 0's after I add more and more worksheets for the formula to go through.

Are the codes and values in the same ranges on every sheet?

The ranges I set are the most stretched ones from the longest worksheet.
Not every worksheet is the same. The ranges on the worksheets are varied.
Because they are based on the data downloaded from the scanner.
Every download produced a different amount of lines.
So I decided to write the formula for the worksheet with the most lines assuming that if other worksheets are shorter then it will just have fewer lines to check.

 

[jasonb75]

How I can check if it is set to automatic or not?

Click 'File' on the excel ribbon, then go to 'Options' and 'Formulas'. Calculation options will be at the top of the list.

I am not sure I understand the question.

Let's say that your formula is in column D of Sheet1, if Sheet1 is in 'listsheets' and column C contained a match for the code in A2 then it would cause a circular reference because the formula would be trying to add itself to itself. As a simple example, typing =A1 into A1 would create a circular reference.

The ranges I set are the most stretched ones from the longest worksheet.

With that, as long as the columns are the same then it should work fine.

It could be possible that some of the data is not a perfect download from the source. Data from web pages (for example) can often contain zero width spaces, hidden characters that are not visible but prevent formulas from matching correctly.

If you know of a specific sheet / code combination that is not returning a result when it should then checking the source cells could help to identify errors.

=Sheet2!C2=A2

would return TRUE if C2 of sheet2 is an identical match to A2.

=Sheet2!D2+0

would identify if the quantity in column D is a valid number by either returning the same number or a #VALUE! error.

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sumproduct returns 0
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.