SUMPRODUCT function

littlenathan

New member
Joined
Apr 8, 2014
Messages
5
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try

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

littlenathan

New member
Joined
Apr 8, 2014
Messages
5
Reaction score
0
Points
0
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:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
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.
 

Attachments

  • ExcelGuru2895.xlsx
    13.9 KB · Views: 19

littlenathan

New member
Joined
Apr 8, 2014
Messages
5
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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
 

littlenathan

New member
Joined
Apr 8, 2014
Messages
5
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

littlenathan

New member
Joined
Apr 8, 2014
Messages
5
Reaction score
0
Points
0
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
 
Top