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]

Same error message.

What problems do I need to be looking for? Data Type?

 

[markmzz]

My data



<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">2</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">2</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,0</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">46370,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,0</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">=MIN(SUMPRODUCT(--(I5:I6000="PRJ"),--(J5:J6000="PRJ"),--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(M5:M6000<=TODAY())),SUM(IF(FREQUENCY(K5:K6000,K5:K6000)>0,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(K5:K6000,K5:K6000)>0,1))</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

 

[markmzz]

Do you have any formula in the column O?

Markmzz

 

[shdawson]

Hey, see an oddity.

You are using 46370,0
My data has 46370
Data is formated at 46370.0

Comma -versus- Decimal



Now, some of the data has .3, or .2, or whatever. An example of this is:

48501
48501.2
48501.3
48501.4
48501.5


That being the case, it seems the comma versus decimal is the culpret, yet?

 

[markmzz]

Hey, see an oddity.

You are using 46370,0
My data has 46370
Data is formated at 46370.0

Comma -versus- Decimal



Now, some of the data has .3, or .2, or whatever. An example of this is:

48501
48501.2
48501.3
48501.4
48501.5


That being the case, it seems the comma versus decimal is the culpret, yet?

In my regional language setting, the decimal separator is , and not .

New configuration English (USA)

Excel 2003<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">6/7/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/1/2011 0:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/30/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">6/7/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">5/19/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/5/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">525</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/7/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">4/9/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4/20/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/7/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">6/1/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/18/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3138</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/7/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">5/30/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/19/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4719</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/7/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">4/14/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4/30/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5197</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/7/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">5/7/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5/22/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5936</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/7/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">4/17/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5/1/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">=MIN(SUMPRODUCT(--(I5:I6000="PRJ"),--(J5:J6000="PRJ"),--(A5:A6000='Release Chart - CALCUATIONS'!A5),--(M5:M6000<=TODAY())),SUM(IF(FREQUENCY(K5:K6000,K5:K6000)>0,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(K5:K6000,K5:K6000)>0,1))</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

 

[markmzz]

Could you send me the sheets (with fictitious data)? So I can test them.

Markmzz

 

[shdawson]

Found #VALUE! error.

I had...
"PRJ"),SUM


You had...
"PRJ")),SUM


So, the error is gone. But, still getting a result of 12.

I cannot send the worksheet, due to company regulations. Pressing on past the #VALUE! error, now focusing on why I am getting 12 and you are getting 2.

Any thoughts on that part?


Thanks,
SHD

 

[shdawson]

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

This returns 115. It should be 20,006.


This code is not perfoming on my end like your end.


hmmmm.....

 

[markmzz]

WOW! That is code!

I entered:

[COLOR=blue][B]SUM(IF(FREQUENCY('Project Task'!O$2:O$60000,'Project Task'!O$2:O$60000)>0,1))[/B][/COLOR]

...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.
SHD

Your formula here is not wrong

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

Look at my

=SUM(IF(FREQUENCY(K5:K6000,K5:K6000)>0,1))

So, the problem is here.

Try it again.

Markmzz

 

[shdawson]

Yes, I did have the = sign, I just did not paste it into my posting.

It does not appear your code is being evaluated by my installation of Excel 2003. I have the Analysis Toolpak installed and the check-box checked.

Is there another formula we can try to see if the functionality is working as expected? This will help to (hopefully) narrow down the difference between your Excell and mine. Are your running Excel 2003?