Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    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. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    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
    Attached Files Attached Files

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Why should they be the same?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    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. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The source data is text, not numbers, so the TEXT function is used to convert the values in E3 and G3 to text too.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    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
    Attached Files Attached Files

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    verada (2015-11-13)

  11. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    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
  •