Results 1 to 9 of 9

Thread: SUMPRODUCT function

  1. #1

    SUMPRODUCT function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Guru,

    I am a newbie member here in this forum. Further to my visit I observed that a lot of questions been answered so I decided to post my question.

    I have been given a newly assignment monitoring of management study activities in the plant. And dealing with an excel work book every day with a huge of action items has been entered since 1990ís. There were 3,200+ action items has been generated since in the beginning. Now as you can see on the table above I would to calculate the number of action items that has been closed every month and year.

    From the table below, I would like to develop a pick drop down list instead of listing all categorically. Based on the example there were 4 action items has been closed of year 2013 while there were 2 action items has been closed for the month of April 2013.


    Type MTD Closing Date Status Process 2013 April
    Process 10-Jan-12 Closed 4 2
    Process 15-Jan-12 Closed
    Process 8-Mar-13 Closed
    Process 10-Mar-13 Closed
    Process 15-Apr-13 Closed
    Process 16-Apr-13 Closed
    Process 8-Jan-14 Closed
    Process 8-Feb-14 Closed
    Project 5-Mar-12 Closed
    Project 6-Jan-12 Closed
    Project 8-Apr-13 Closed
    Project 10-Apr-13 Closed
    Project 20-Mar-14 Closed
    Project 22-Mar-14 Closed
    OTSHE 11-Jan-12 Closed
    OTSHE 2-Feb-12 Closed
    OTSHE 8-Mar-13 Closed
    OTSHE 22-Mar-13 Closed
    OTSHE 10-Apr-14 Closed
    OTSHE 12-Apr-14 Closed


    Iíve come up a calculation method using SUMPRODUCT function to get the number that has been closed for year 2013. Please see below method.

    =SUMPRODUCT(($A$2:$A$21=$D1)*(YEAR($B$2:$B$21)=E$2)*($C$2:$C$21="Closed"))

    Using this method I get exactly the result. So If changing the year into 2014 category Process using drop down list again, exactly I get the result of 2.

    The question now is to define the correct method using a drop down list of monthly category. E.g. If I am going to change the year into January 2014 I should get a result of 1. I have been struggling for several days on how to calculate the result. I have been trying to use this method but unfortunately not working.

    =SUMPRODUCT(($A$2:$A$21=$D1)*(TEXT($B$2:$B$21,"mmmm")=F$2)*($C$2:$C$21="Closed"))

    Please I need your help guys and really appreciated of any assistance as I am not skillful of logical function in excel worksheet.

    Best Regards,
    Littlenathan

  2. #2
    Try

    =SUMPRODUCT(($A$2:$A$21=$D1)*(TEXT($B$2:$B$21,"mmmm yyyy")=E$1)*($C$2:$C$21="Closed"))

  3. #3
    Dear Bob,

    Appreciate your quick response. However, the method you provided is not working. Whenever I change the month January 2014 I should get a result of 1 while there were 2 action items has been closed for year 2014.

    While in this principle it remains a result of 1 while changing into another month e.g. March 2014 under Process category.


    Best Regards,

    littlenathan
    Last edited by littlenathan; 2014-04-08 at 06:46 PM.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Looking at your formulae it looks as though your Type is in D1, your Year in E2 and your Month in F2, so sticking with that your 2 formulae might be:
    =SUMPRODUCT(($A$2:$A$21=$D1)*(YEAR($B$2:$B$21)=E$2)*($C$2:$C$21="Closed"))
    and:
    =SUMPRODUCT(($A$2:$A$21=$D1)*(YEAR($B$2:$B$21)=E$2)*(TEXT($B$2:$B$21,"mmmm")=F$2)*($C$2:$C$21="Closed"))

    However, the way your data is arranged makes it ideal for a pivot table; see attached.
    Attached Files Attached Files

  5. #5
    Hi p45cal,

    Thank you so much with your valuable input. Yes you are right I was really exhausted to complete this task, because I am running out of time to generate our report this comin Thursday (monthly KPI) apologies as i was so exhausted.

    Perfectly using your method I can now start preparing our monthly KPI. Thank you so much it is really a perfect logic matching my requirements. I do really appreciate your help. And looking your pivot table it is really identical to use as it is clearly and readable and I believe through your sample pivot table I can start immediately to develop graph presentation this is a perfect combination. I will go through it so no more headaches.

    Again, thank you so much.

    Best Regards,

    Littlenathan

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    littlenathan, please post links to all forums that you crossposted at. You have several people working on the same problem and we don't know it.

    Please read this article to understand the problem: http://www.excelguru.ca/content.php?184


  7. #7
    Dear Moderator,

    I do sincerely ask apologies on this, it is not my intention to have a cross post but purely to re-evaluate my structure and I really do not wish to sound like a rude.

    Sincerely and agree that I will absolutely abide the cross post.

    Again, I do sincerely ask apologies from the team.

    Best Regards,

    Littlenathan

  8. #8
    Nathan,

    Whilst we may prefer you not to cross-post, the main thrust of the article is asking to post links when you do cross-post. We as individuals can they either choose to ignore that post, check to see if the other places are dealing with it, or just answer it. Just a matter of being fair to everyone, including those who offer their help.

  9. #9
    Dear Moderator,

    Again, I have no intention to do the same purely to reevaluate my constructive logical function and no intention at all or to disobey the roles and procedure. I had only once a cross post.

    I had already answer on the other side.

    Again sincerely no intention at all.



    Best Regards,

    Littlenathan

Posting Permissions

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