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

 

[markmzz]

I found the problem:

Look at this example

<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></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Total # not equal</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>>2 and <9</TD><TD style="TEXT-ALIGN: right">6</TD><TD>3 # not equal</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>>2 and <9 unique</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right"></TD><TD>********</TD><TD style="TEXT-ALIGN: right"></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>B12</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B14</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY((A1:A10>2)*(A1:A10<9)*(A1:A10),(A1:A10>2)*(A1:A10<9)*(A1:A10))>0,1))-1</TD></TR></TBODY></TABLE></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">Array 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>B13</TH><TD style="TEXT-ALIGN: left">{=SUM((A1:A10>2)*(A1:A10<9))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself





</TD></TR></TBODY></TABLE>

The my formula of frequency must be this:

=SUM(IF(FREQUENCY((I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=HOJE())*(K5:K6000);(I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=HOJE())*(K5:K6000))>0,1))-1

With all array in the frequency formula.

Markmzz

 

[shdawson]

Found the problem. The problem is in part B of the FREQUENCY formula.

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

Again, this is only part of the entire formula. The entire formula is:

=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,M2:M60000)>0,1)))

Now, this is returning 1, and not 2. So, the logic is not evaulating the same on my Excel 2003 as is your installation of Excel.


Oh, so close!!! Hmmmm.......

 

[shdawson]

Hmmm...you have new input, for sure.

Please review my entry about the different cell range for part B and let me know what code option to try.


Thank you very, very, very much.


Kindly,
SHD

 

[shdawson]

BTW, what is HOJE...please?

 

[markmzz]

BTW, what is HOJE...please?

TODAY

Markmzz

 

[markmzz]

Now I think it's ok.


<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: center; BACKGROUND-COLOR: #ccccff">With Freq</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Whitout Freq</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></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: center; BACKGROUND-COLOR: #ccccff"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Freq</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff"></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 style="BACKGROUND-COLOR: #ccccff">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: #ccccff">46370,5</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: #ccccff">46370,0</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: #ccccff">48622,0</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: #ccccff">48622,0</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: #ccccff">48622,0</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: #ccccff">46370,5</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: #ccccff">48622,0</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>D1</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY((I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=TODAY())*(K5:K6000),
(I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=TODAY())*(K5:K6000))>0,1))-1


</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>E2</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY((I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=TODAY())*(K5:K6000),
(I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=TODAY())*(K5:K6000))>0,1))-1


</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


No need of SUMPRODUCT formula

Markmzz

 

[shdawson]

Gracious!

Can you please walk me through your formula, from layman's words? The asterisks, I guess that is how an array is performed instead of hyphen hyphen (--).


Wow, this is quite a formula indeed.


Thank You,
SHD

 

[markmzz]

Shdawson,

I'll try to explain using examples with comments and the help of Excel.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Help of Excel
<o></o>
<o></o>
FREQUENCY function
<o></o>
<o></o>
Calculates how often values occur within a range of values and then returns a vertical array of numbers.
For example, use FREQUENCY to count the number of test results. Because FREQUENCY returns an array, it must be entered as an array formula. <o></o>
<o></o>

Syntax <o></o>
<o></o>

Frequency (data_array, bins_array) <o></o>
<o></o>

Data_array is an array or a reference to a set of values for which you want to count frequencies.
If data_array contains no values, FREQUENCY returns an array of zeros.
<o></o>
<o></o>
Bins_array is an array or reference to intervals into which you want to group the values in data_array.
If bins_array contains no values, FREQUENCY returns the number of elements in data_array.
<o></o>
<o></o>
Comments
<o></o>
<o></o>
FREQUENCY is entered as an array formula after you selected a range of adjacent cells into which you want the distribution provided to appear.
<o></o>
<o></o>
The number of elements in the returned array is one more than the number of elements in bins_array.
The additional element of the returned array displays the count of values that are above the highest range.
For example, when counting three ranges of values entered into three cells, be sure to enter the FREQUENCY
into four cells for the results. The extra cell returns the number of values in data_array that are greater than the third interval value.
<o></o>
<o></o>
FREQUENCY ignores blank cells and text.
<o></o>
<o></o>
Formulas that return arrays must be entered as array formulas.<o></o>

Examples:

Sumproduct
<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></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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Numbers</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Functions Arguments</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUMPRODUCT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">(A2:A11>2)</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">{FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">3</TD><TD style="TEXT-ALIGN: center">(--(A2:A11>2))</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">4</TD><TD style="TEXT-ALIGN: center">{0,0,1,1,1,1,1,1,0,1}</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">5</TD><TD style="TEXT-ALIGN: center">(A2:A11>2) (A2:A11<9) (A2:A11)</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">4</TD><TD style="TEXT-ALIGN: center">{FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE} {FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE} {1,1,3,3,4,4,5,4,1,10}</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center; COLOR: #0070c0; FONT-WEIGHT: bold">SUMPRODUCT treats array entries not numeric as if they were zeros</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">(A2:A11>2)*(A2:A11<9)*(A2:A11)</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">23</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">{0,0,3,3,4,4,5,4,0,0}</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; COLOR: #0070c0; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">(--(A2:A11>2)) (--(A2:A11<9)) (A2:A11)</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">23</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">{0,0,1,1,1,1,1,1,0,1} {1,1,1,1,1,1,1,1,1,0} {1,1,3,3,4,4,5,4,1,10}</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>*********</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">*************</TD><TD style="TEXT-ALIGN: center">*************</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>C2</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(A2:A11>2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A2:A11>2))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C8</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((A2:A11>2),(A2:A11<9),(A2:A11))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C11</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((A2:A11>2)*(A2:A11<9)*(A2:A11))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C14</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((--(A2:A11>2)),(--(A2:A11<9)),(A2:A11))</TD></TR></TBODY></TABLE></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">Array 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>D2</TH><TD style="TEXT-ALIGN: left">{=SUM(A2:A11>2)}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D5</TH><TD style="TEXT-ALIGN: left">{=SUM(--(A2:A11>2))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D11</TH><TD style="TEXT-ALIGN: left">{=SUM((A2:A11>2)*(A2:A11<9)*(A2:A11))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D14</TH><TD style="TEXT-ALIGN: left">{=SUM((--(A2:A11>2))*(--(A2:A11<9))*(A2:A11))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


</TD></TR></TBODY></TABLE>

Frequency
<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></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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Numbers</TD><TD style="FONT-WEIGHT: bold">Total of numbers</TD><TD style="TEXT-ALIGN: right">10</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Total of numbers uniques</TD><TD style="TEXT-ALIGN: right">5</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Total of numbers between 2 and 9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">6</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">3</TD><TD style="FONT-WEIGHT: bold">Total of numbers uniques between 2 and 9</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">=(--(A2:A11>2))*(--(A2:A11<9))*(A2:A11)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">3</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">{0,0,3,3,4,4,5,4,0,0}</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">4</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">4</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">=FREQUENCY((A2:A11>2)*(A2:A11<9)*(A2:A11),(A2:A11>2)*(A2:A11<9)*(A2:A11))</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">5</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">{4,0,2,0,3,0,1,0,0,0,0}</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">4</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">4 - zeros, 2 - three, 3 - four and 1 - five</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">1</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">10</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">=IF(FREQUENCY((A2:A11>2)*(A2:A11<9)*(A2:A11),(A2:A11>2)*(A2:A11<9)*(A2:A11))>0,1)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</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">{1,FALSE,1,FALSE,1,FALSE,1,FALSE,FALSE,FALSE,FALSE}</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</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">=SUM(IF(FREQUENCY((A2:A11>2)*(A2:A11<9)*(A2:A11),(A2:A11>2)*(A2:A11<9)*(A2:A11))>0,1))-1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</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">{1,FALSE,1,FALSE,1,FALSE,1,FALSE,FALSE,FALSE,FALSE}</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</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">-1 - To remove the zero</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>*********</TD><TD>***************************************</TD><TD>******</TD><TD>******</TD><TD>******</TD><TD>***</TD><TD style="TEXT-ALIGN: right"></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>D3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((A2:A11>2),(A2:A11<9))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left">=COUNT(A2:A11)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY(A2:A11,A2:A11)>0,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A2:A11>2),--(A2:A11<9))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY((A2:A11>2)*(A2:A11<9)*(A2:A11),(A2:A11>2)*(A2:A11<9)*(A2:A11))>0,1))-1</TD></TR></TBODY></TABLE></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">Array 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>E3</TH><TD style="TEXT-ALIGN: left">{=SUM((A2:A11>2)*(A2:A11<9))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


</TD></TR></TBODY></TABLE>


I hope that helps

Markmzz

 

[shdawson]

Mark,


Good morning. You have quite a formula here. Well done!

Your Formula:

=SUM(IF(FREQUENCY((I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=TODAY())*(K5:K6000),
(I5:I6000="PRJ")*(J5:J6000="PRJ")*(A5:A6000='Release Chart - CALCUATIONS'!A5)*(M5:M6000<=TODAY())*(K5:K6000))>0,1))-1

My Edit of Your Formula:

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

Comments:
-I used a cell with a date, you are using the TODAY() function.
-Cell 'Release Chart - LOG'!J$3 is 6/1/2011 12:00:00 AM
-I inserted another qualifier, ('Project Task'!P$2:P$60000>0).
-I stuck the temporary data far to the right, knowing good and well I have plent of room for further growth, on cells (DD$2:DD$60000).


I did apply Control+Shift+Enter. The curly brackets are in place.

The good news is....the formula evaluates. The bad news is...the evaluation is not correct. I get 0 for all formula evaluations.

What is the best path to track down the problem?


Thank You,
SHD

 

[markmzz]

Shdawson,

Your formula have a error (you loose a part in the second argument of the function frequency)

Important: no need of Ctrl+Shift+Enter

Look at this:

=SUM(IF(
FREQUENCY(
[COLOR=blue][B]('Project Task'!P$2:P$60000>0)[/B][/COLOR]*('Project Task'!I$2:I$60000="PRJ")*
('Project Task'!J$2:J$60000="PRJ")*
('Project Task'!A$2:A$60000='Release Chart - CALCUATIONS'!A2)*
('Project Task'!M$2:M$60000>='Release Chart - LOG'!J$3)*(DD$2:DD$60000),
 
('Project Task'!I$2:I$60000="PRJ")*('Project Task'!J$2:J$60000="PRJ")*
('Project Task'!A$2:A$60000='Release Chart - CALCUATIONS'!A2)*
('Project Task'!M$2:M$60000>='Release Chart - LOG'!J$3)*(DD$2:DD$60000))>0,1))-1

Here is my data modified (I use data of only one sheet to facilitate):

<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: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Result</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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></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: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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-CALC</TD><TD style="BACKGROUND-COLOR: #ffff00">RC-LOG</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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">Only 2 not equal values</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BACKGROUND-COLOR: #ffff00; 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"></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Tower</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col02</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col03</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col04</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col05</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col06</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col07</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col08</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Task Type</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">MileType</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col09</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col10</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Current Finish Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col11</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Control #</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">% Done</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">64</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">04/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">46370,0</TD><TD style="TEXT-ALIGN: center">69%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1465</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">01/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">48622,0</TD><TD style="TEXT-ALIGN: center">9%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1662</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">13%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2066</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">22/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">97%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2163</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">30/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">48622,0</TD><TD style="TEXT-ALIGN: center">97%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2659</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">12/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">48622,0</TD><TD style="TEXT-ALIGN: center">33%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2839</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">30/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">48622,0</TD><TD style="TEXT-ALIGN: center">2%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3963</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">48622,0</TD><TD style="TEXT-ALIGN: center">90%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4590</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">30/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">46%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4630</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">10/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">1%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4748</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">87%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4808</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">20/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">85%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5170</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">10/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">70%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5482</TD><TD style="TEXT-ALIGN: center">07/06/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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/07/2011</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">46370,0</TD><TD style="TEXT-ALIGN: center">88%</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>D1</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY((P12:P6007>0)*(I12:I6007="PRJ")*(J12:J6007="PRJ")*(A12:A6007=K7)*(M12:M6007>=L7)*(O12:O6007),
(P12:P6007>0)*(I12:I6007="PRJ")*(J12:J6007="PRJ")*(A12:A6007=K7)*(M12:M6007>=L7)*(O12:O6007))>0,1))-1

</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz