• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populating how many project started by matching its name within any given month

T

tonykyo

New Member
Hi guys,


I'm honing my excel skills because I am required to pick up someone else's work on Excel. Naturally I am having some difficulties with it, in particular below code.


=SUMPRODUCT((rProjectType=$C34)*(rActualStartDate<EOMONTH(DATE($B34,E$32,1),0))*(rActualEndDate>EOMONTH(DATE($B34,E$32,1),0)))+SUMPRODUCT((rProjectType=$C34)*(rActualStartDate<EOMONTH(DATE($B34,E$32,1),0))*(rActualEndDate=""))


With the code above I get counts on a specific project every month as long as it not closed, i simply wish to calculate how many project is opened within a specific month for a project not considering whether its still open or closed.


Can anyone help please?


DATA content (in a separate tab) - If you imagine:

rProjectType data e.g. ProjectA, ProjectB, ProjectC, ProjectD

rActualStartDate data e.g. 01/02/2009, 18/02/2010

rActualEndDate data e.g. 30/08/2009, 30/09/2010

B34 data is the year e.g. 2009, 2010, 2011

E32 data is the month e.g. 1, 2, 11, 12


Many thanks in advanced! :)
 
Maybe this?

=SUMPRODUCT((rProjectType=$C34)*(rActualStartDate<EOMONTH(DATE($B34,E$32,1),0))*((rActualEndDate>EOMONTH(DATE($B34,E$32,1),0))+(rActualEndDate="")))


Checks if rProjectType is correct, StartDate is before the criteria data, and EndData is either after criteria date, or not filled in.
 
Thanks a lot Luke! I will give this ago right away. Sorry for not responding any earlier, I was expecting an automated emails from chandoo.org when there is respond.
 
You're welcome.

No worries about the delayed response. Sorry site doesn't do emails...if you click on your name, you can see if anyone has replied to any of your recent postings, which might help in future.

http://chandoo.org/forums/profile/tonykyo
 
Hi Luke, it appears that the calculation still produces the same data. I wish to just take the rActualEndDate out of the equation. So instead I will just concentrate on one column rActualStartDate to see how many project was opened on a specific month, and this formula will be pasted in the column (going through the record) where the result of how many project for e.g. January 2009, february 2009 and so on is going to be.
 
$C34 = Project1

B34 = 2009

E32 = 1 (january)


=SUMPRODUCT(rProjectType=$C34)*(rActualStartDate>EOMONTH(DATE($B34,E$32-1,1),0)*(rActualStartDate<EOMONTH(DATE($B34,E$32,1),0)))


I still get 0! What have I done wrong?
 
You must log in or register to reply here.
Back
Top