Question about Date Range

[shdawson]

Hi,


Have the following formula:

=SUMPRODUCT(--('Project Task'!A5:A$60000='Release Chart - CALCUATIONS'!A5),--('Project Task'!I5:I$60000="PRJ"),--('Project Task'!J5:J$60000="PRJ"),--('Project Task'!L5:L$60000<='Release Chart - LOG'!J$3),--('Project Task'!M5:M$60000>='Release Chart - LOG'!J$4))

'Release Chart - LOG'!J$3 = 6/1/2011 12:00:00 AM
'Release Chart - LOG'!J$4 = 6/30/2011 11:59:59 PM


Here is my problem. I have a project that starts on 6/7/2011. So, my count is off by 1.

Clearly, the logic is not correct on the date consideration.

I need to count the number of unique entires in 'Project Task'!A5:A$60000 that have any presence in the month of June, for 'Release Chart - CALCUATIONS'!A5.

Rather stuck on the date consideration. Not sure what to do. Please advise.

Thank you very much.


Kindly,
SHD

 

[shdawson]

Hi,


I reviewed the formula against all of the data with the built in Data Filtering of Excel. 100% spot on. I am tired, so will review again tomorrow.

Talked to my manager before I left. He validated will need another criteria in the current formula.

This current formula traps entries in column A, which is the department name (a text field), measuring the different criteria of PRJ, finish date, and what not. This current result set tells how many software releases each department will have for the calendar month.

The need is to further restrict the result set by project number. In the current result set of 17 releases, there are actually 2 different department numbers. So, the result I will need is 2, not 17. The project number is a number field, in it's own column. Generically, I believe I need the DISTINCT entries of the project number in the result set the current formula already provides.

Not sure how to go about this. SELECT DISTINCT PROJ_NUM sounds good to me, but this is not SQL.

Any recommendations?


Thank You,
SHD

 

[shdawson]

The need is to further restrict the result set by project number. In the current result set of 17 releases, there are actually 2 different department numbers. So, the result I will need is 2, not 17.

Sorry, this section is not clear. This example of 17 different software releases is for only 1 department with 2 different project numbers.

Apologizes for the confusion.

 

[markmzz]

Could you put an example of each worksheet (Release Chart – LOG, Release Chart – CALCUATIONS and Project Task) necessary for the calculation you want?

Use the following link helper to do this.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz

 

[shdawson]

Good Morning,


Working on posting the requested data.

Question, please:
Does the SUMPRODUCT work from an "and" perspective? In other words, does it matter the order the array's are put in? If so, I need to put array's in differently. I cross-check my SUMPRODCUT with Data/Filter and get different results, based upon the order I apply the filters.


Thanks,
SHD

 

[markmzz]

Good Morning,


Working on posting the requested data.

Question, please:
Does the SUMPRODUCT work from an "and" perspective? In other words, does it matter the order the array's are put in? If so, I need to put array's in differently. I cross-check my SUMPRODCUT with Data/Filter and get different results, based upon the order I apply the filters.


Thanks,
SHD

No. Look at this (C1;D1;E1 and L1):


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">RC</TD><TD style="BACKGROUND-COLOR: #ffff00">DTI</TD><TD style="BACKGROUND-COLOR: #ffff00">DTF</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">FILTER</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011 00:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/06/2011 23:59</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Col01</TD><TD>Col02</TD><TD>Col03</TD><TD>Col04</TD><TD>Col05</TD><TD>Col06</TD><TD>Col07</TD><TD>Col08</TD><TD>Col09</TD><TD>Col10</TD><TD>Col11</TD><TD>Col12</TD><TD>Col13</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">55</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">19/05/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">05/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">525</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">09/04/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">20/04/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">18/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3138</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/05/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">19/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4719</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">14/04/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/04/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5197</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/05/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">22/05/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5936</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">17/04/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/05/2011</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(I5:I6000="PRJ"),--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(J5:J6000="PRJ"),--(M5:M6000<=TODAY()))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(I5:I6000="PRJ"),--(J5:J6000="PRJ"),--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(M5:M6000<=TODAY()))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(M5:M6000<=TODAY()),--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(I5:I6000="PRJ"),--(J5:J6000="PRJ"))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>L1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(I5:I6000="PRJ"),--(J5:J6000="PRJ"),--(M5:M6000<=TODAY()))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>M1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(I5:I6000="PRJ"),--(J5:J6000="PRJ"),--(TEXT(L5:L6000,"maaaa")="62011"),--(TEXT(M5:M6000,"maaaa")="62011"))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>K3</TH><TD style="TEXT-ALIGN: left">='Release Chart - CALCUATIONS'!A5</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>L3</TH><TD style="TEXT-ALIGN: left">='Release Chart - CALCUATIONS'!J3</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>M3</TH><TD style="TEXT-ALIGN: left">='Release Chart - CALCUATIONS'!J4</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[shdawson]

Good Morning,


My current entry in 'Release Chart - CALCUATIONS'!A9 is:
HRZ

My current entry in 'Release Chart - LOG'!J3 is:
06/01/2011 12:00:00 AM


Here is my current formula:

=SUMPRODUCT(--('Project Task'!P$2:P$60000>0),--('Project Task'!M$2:M$60000>='Release Chart - LOG'!J$3),--('Project Task'!A$2:A$60000='Release Chart - CALCUATIONS'!A9),--('Project Task'!I$2:I$60000="PRJ"),--('Project Task'!J$2:J$60000="PRJ"))

This hits the data set on the worksheet "Project Task", as filtered below:

Excel Workbook
	A	I	J	M	O	P
1	Tower	Task Type	Mile Type	Current Finish Date	Control #	% Done
24937	HRZ	PRJ	PRJ	9/16/11	46370.0	5%
24942	HRZ	PRJ	PRJ	9/23/11	46370.0	6%
25001	HRZ	PRJ	PRJ	10/13/11	46370.0	71%
25025	HRZ	PRJ	PRJ	9/15/11	46370.0	13%
25041	HRZ	PRJ	PRJ	7/15/11	46370.0	47%
25044	HRZ	PRJ	PRJ	10/13/11	46370.0	25%
25050	HRZ	PRJ	PRJ	7/5/11	46370.0	54%
25057	HRZ	PRJ	PRJ	7/25/11	46370.0	75%
25069	HRZ	PRJ	PRJ	7/5/11	46370.0	64%
25072	HRZ	PRJ	PRJ	6/17/11	46370.0	96%
25081	HRZ	PRJ	PRJ	6/17/11	46370.0	94%
25221	HRZ	PRJ	PRJ	11/14/11	48622.0	32%
Project Task

Excel 2003


The result is 12, which is the correct answer. This formula needs to be enhanced to count only the unique entries in the column O. That answer should be 2.

How can this be accomplished, please?

Thank you very much.


Kindly,
SHD

 

[markmzz]

Try this formula:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Tower</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Release Chart - LOG'!J3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Release Chart - CALCUATIONS'!A9 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Task</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Mile</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Current</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Control</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">06/01/2011 00:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Type</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Type</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Finish</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">#</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Done</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">16/09/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">5%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">23/09/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">6%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">13/10/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">71%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">15/09/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">13%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">15/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">47%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">13/10/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">25%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">05/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">54%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">25/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">75%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">05/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">64%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">17/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">96%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">17/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">46370</TD><TD style="TEXT-ALIGN: center">94%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">HRZ</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center">PRJ</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">14/11/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">48622</TD><TD style="TEXT-ALIGN: center">32%</TD></TR></TBODY></TABLE>

Plan2​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D3</TH><TD style="TEXT-ALIGN: left">=MIN(SUMPRODUCT(--(P4:P15>0),--(M4:M15>=C2),--(A4:A15=D2),--(I4:I15="PRJ"),--(J4:J15="PRJ")),SUM(IF(FREQUENCY(O4:O15,O4:O15)>0,1)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[shdawson]

WOW! That is code!

I entered:

SUM(IF(FREQUENCY('Project Task'!O$2:O$60000,'Project Task'!O$2:O$60000)>0,1))

...and:

--SUM(IF(FREQUENCY('Project Task'!O$2:O$60000,'Project Task'!O$2:O$60000)>0,1))

Both are giving me the dreaded #VALUE! error. Hmmm.....so close.



Complete Formula:

=MIN(SUMPRODUCT(--('Project Task'!P$2:P$60000>0),--('Project Task'!M$2:M$60000>='Release Chart - LOG'!J$3),--('Project Task'!A$2:A$60000='Release Chart - CALCUATIONS'!A9),--('Project Task'!I$2:I$60000="PRJ"),--('Project Task'!J$2:J$60000="PRJ"),SUM(IF(FREQUENCY('Project Task'!O$2:O$60000,'Project Task'!O$2:O$60000)>0,1))))

Figure out this last hitch, and that ought to be it.


Correct a #VALUE! error
Occurs when the wrong type of argument or operand is used.


Your code clearly works, but mine does not. mmmmm.......I am becoming dazed and confused.



SHD

 

[shdawson]

Reading the Help File about FREQUENCY function. Says must use CTRL+SHIFT+ENTER to run this function. Not sure how to apply to just the FREQENCY function. I tried the whole function, still have #VALUE! error.


Hmmmm.......

 

[markmzz]

Reading the Help File about FREQUENCY function. Says must use CTRL+SHIFT+ENTER to run this function. Not sure how to apply to just the FREQENCY function. I tried the whole function, still have #VALUE! error.


Hmmmm.......

Try it with Ctrl+Shift+Enter. No need here (sum of values) but.

Markmzz