SUMPRODUCT function

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.




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

Try

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

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

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.

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

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

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

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.

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