+ Reply to Thread
Results 1 to 8 of 8

Problems with SUMPRODUCT -Need help badly!!

  1. 12-03-2012, 02:50 PM #1
    sav1979
    sav1979 is offline
    Registered User
    Join Date
    07-01-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    61

    Problems with SUMPRODUCT -Need help badly!!

    Hi all, this is driving me mad and I know one of you kind soles will come to my rescue!!

    I am trying to calculate using SUMPRODUCT in order to get results from 2 columns. The first column shows the test priority (column D) and the second shows the result (column E). There are 4 levels of priority - Critical, high, medium, low and 6 results - 1 and 2 is fail, 3 is Quality Issue, 4 and 5 are pass and 'pending' is not yet carried out.

    In the matrix table, I want to show The number of tests per category, ie. That x amount of critical tests have passed, y have failed, z are pending and so on and so forth.

    I am looking forward to your expert advice and thank you in advance.

    Cheers
    Attached Files Attached Files
    Last edited by sav1979; 12-03-2012 at 05:05 PM.
    Reply With QuoteRegister To Reply
  2. 12-03-2012, 03:36 PM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Problems with SUMPRODUCT -Need help badly!!

    Does the attached get you on track?

    Copy of SUMPRODUCT example.xlsx
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 12-03-2012, 03:53 PM #3
    sav1979
    sav1979 is offline
    Registered User
    Join Date
    07-01-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    61

    Re: Problems with SUMPRODUCT -Need help badly!!

    Hi Pepe, many thanks for your post. Actually, this still shows an incorrect result. If you look at cell L11, it shows that 2 'critical' tests have 'passed'. Actually, there is only one critical test that has passed (E19), as the other one is a 'high' priority (E15). Any ideas??
    Thanks
    Reply With QuoteRegister To Reply
  4. 12-03-2012, 03:58 PM #4
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Problems with SUMPRODUCT -Need help badly!!

    Try =SUMPRODUCT(($D$7:$D$22=L$9)*($E$7:$E$22=4)+($E$7:$E$22=5)) in L11 ( the last critical is " pending" in the example)
    Reply With QuoteRegister To Reply
  5. 12-03-2012, 04:13 PM #5
    sav1979
    sav1979 is offline
    Registered User
    Join Date
    07-01-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    61

    Re: Problems with SUMPRODUCT -Need help badly!!

    Thanks again.

    Ok, I tried that which seemed to look good. Then when I tested it by changing around some of the test results in column E, I started getting issues again. If you change a few test results it always seems to upset the other results. Take a look at the attachment now which I have updated with the formulae. To me, this looks correct but always fails upon changing around the test results

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 12-03-2012, 04:37 PM #6
    Pauleyb
    Pauleyb is offline
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Problems with SUMPRODUCT -Need help badly!!

    Just looking at that formula, I think you have an operator precedence issue. The multiplication happens and then the add; I think you want the add to occur first. Just add some parens.
    =SUMPRODUCT(($D$7:$D$22=L$9)*(($E$7:$E$22=4)+($E$7:$E$22=5)))
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
    Reply With QuoteRegister To Reply
  7. 12-03-2012, 04:49 PM #7
    sav1979
    sav1979 is offline
    Registered User
    Join Date
    07-01-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    61

    Re: Problems with SUMPRODUCT -Need help badly!!

    Pauley, you, my man are a genius. Thanks so much, that appears to solve the problem. I have tested and retested. Such a simple fix but one which I have overlooked time and time again!! Thanks a lot

    Thanks to Pepe also, much appreciated my friend.
    Reply With QuoteRegister To Reply
  8. 12-03-2012, 04:53 PM #8
    sav1979
    sav1979 is offline
    Registered User
    Join Date
    07-01-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    61

    Re: Problems with SUMPRODUCT -Need help badly!!

    Pauley, you, my man are a genius. Thanks so much, that appears to solve the problem. I have tested and retested. Such a simple fix but one which I have overlooked time and time again!! Thanks a lot

    Thanks to Pepe also, much appreciated my friend.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1