1. ## Help with sumproduct

Hi All,

I need some expert help with this one - I can't work out what the problem is.

In the attached file in the "Lead Indicators" tab, you can see the columns containing the =sumproduct formulas.

Column C & D work fine, but when I add another criteria to the formulas in E & F and G & H is where the problem is. Either not returning a value or giving a #N/A error.

Any thoughts or suggestion would be very much appreciated

Regards

2. You haven't made some of the range references absolute, but more importantly the data in column O on the close out sheet is text, not actual numbers, so you need:
=SUMPRODUCT(('Investigation Close out T&O'!\$N\$8:\$N\$5000>'Investigation Close out T&O'!\$L\$8:\$L\$5000)*('Investigation Close out T&O'!\$D\$8:\$D\$5000=\$B3)*('Investigation Close out T&O'!\$I\$8:\$I\$5000>=\$A\$1)*('Investigation Close out T&O'!\$O\$8:\$O\$5000<=TEXT('Lead indicators'!E\$2,"0")))
in E3 and copy down.

3. Thanks Rory,

That sorted it out

I thought it had something to do with that, but wasn't sure.

Do you think that is is the best formula for this or are there better ways to achieve the result?

Regards

4. ## Can't work this out

Hi All

I can't work this one out (could be that I'm missing something very obvious!)

I've applied Rory's solution above and it looks to be working - sort of.

I've attached the test file. in cells E3 & G3 I've added a drop down so I can test the results. The problem is when I use the same value in these cells the =sumproduct returns different results in the columns below. The way I see it is that the results of the =sumproduct should be the same if the values in E3 & G3 are the same.

I've tried a few different things like the changing the text format to "0.00" and that seamed to work in column E & F but not G & H and not consistently

Any suggestions or soultions would be most appreciated

Regards

5. Why should they be the same?

6. Hi Rory - Of course there is >= & <=in the formulas. slap the forehead!!!

I still don't understand why TEXT('Lead indicators'!E\$3,"0.00"))) & TEXT('Lead indicators'!G\$3,"0"))) make a difference in the results in the >=2 and the <=3 colums.

Any thoughts?

7. The source data is text, not numbers, so the TEXT function is used to convert the values in E3 and G3 to text too.

8. Thanks Rory

The question related more to why having "0.00" & "0" in the formula made a difference to the results when the values in E3 and G3 are both the "0".

If have a look at row 10 & 11 you can see the different results when these change.

Its not a big deal as I worked the text formatting to get the correct results, just trying to understand why the results change.

Regards

9. Your 'number' data in the source column has trailing spaces. I'd change the formula in that column to:

=IF(F8="","",LEFT(F8,FIND(" ",F8)-1)+0)

and then you won't need the TEXT function at all in the summary table.

10. ## The Following User Says Thank You to rory For This Useful Post:

11. Thanks Rory - that explains it

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•