SUMPRODUCT Issue

[Sam Walsh]

Hi All

I have an issue with a SUMPRODUCT formula I have used.

=SUMPRODUCT(('Feed Data'!A$79:A$128='Running Report Sept Onwards'!H123)*('Feed Data'!B$79:B$128='Running Report Sept Onwards'!J123)*('Feed Data'!D$79:D$128='Running Report Sept Onwards'!E123)*('Feed Data'!E$79:E$128='Running Report Sept Onwards'!N123)*('Feed Data'!F$79:F$128))

The formula is looking for essentially a sales price based on 4 other bits of information, the formula is picking up 90% of the values required but missing some in particular, no obvious reason.

Can anybody assist, any obvious errors?

Best

Sam

 

[Fluff]

Hi & welcome to MrExcel.
I can't see anything wrong with your formula. If your criteria are text, then check for typos & leading/trailing spaces.

 

[Sam Walsh]

Thanks Fluff

I have checked the obvious and can't see any obvious issues, however is only on select data so would indicate you are correct.

 

[Fluff]

Are all the criteria text?

 

[Sam Walsh]

They are all text

 

[Fluff]

In that case that's all I can think of.
You should never refer to the sheet name that the formula is on as it can cause problems, so assuming that the formula is on the "Running Report Sept Onwards" sheet you should remove that sheet name from the formula, although I doubt that would cause a problem in this instance.

The only other thing would be to use something like

=H123='Feed Data'!A$93

for the values that are wrong to try & narrow down the problem.

 

[Sam Walsh]

Thanks Fluff

So all criteria match the inputs.

I have noted that is is the final set of criteria causing the problem, if I change it to its alternative IE from RT to 8WT then the formula works and it gives the right output, however when I use RT it doesn't give an output.

Really appreciate your help so far, is there any cause you could see immediately obvious?

Thanks

Sam

 

[Fluff]

If everything works with 8WT as the last criteria, but not with RT, then it suggests that some of the cells with RT may also have leading/trailing spaces or some other hidden character.

 

[Sam Walsh]

They aren't I'm afraid, the RT is bringing some data through on the same lines as I have the same SUMPRODUCT in multiple cells looking of the same array of data and is working on some and not others. Re-checked formatting and input and no issues with spaces or hidden characters.

 

[Fluff]

In that case can you post a small sample of your data that shows the problems.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.