Question about Date Range

shdawson

shdawson

Active Member
Joined
Jan 6, 2007
Messages
381
Hi,


Have the following formula:

Code: 
=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
 
Sort by date Sort by votes
Sorry, not 100% on how you shared the formula is not correct.

I think you are saying it is missing the ('Project Task'!P$2:P$60000>0) in the 2nd, part...yes?

Current Formula:
Code: 
=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'!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))>0,1))-1


Still returning 0.


BTW, what version of Excel are you using, please?


Thanks,
SHD
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Now in Excel 2003 (I modified the data for test - all 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><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; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Result</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">6</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; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Total not equal</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">60</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 6 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">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; BACKGROUND-COLOR: #ffff00">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; BACKGROUND-COLOR: #ffff00">48622,2</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; BACKGROUND-COLOR: #ffff00">48622,1</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,3</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; BACKGROUND-COLOR: #ffff00">46370,5</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; BACKGROUND-COLOR: #ffff00">46370,3</TD><TD style="TEXT-ALIGN: center">88%</TD></TR></TBODY></TABLE>
Project Task​


<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><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D3</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY((O12:O6007),(O12:O6007))>0,1))-1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz
 
Upvote 0
Shdawson,

On various posts (#26, #28 and #42) you used 'Release Chart-CALCUATIONS'!A9 and in the last two used 'Release Chart-CALCUATIONS'!A2.

That is correct?

Markmzz
 
Upvote 0
Yes, I started at the top of the list today to work on the formula. Row 1 is the header. Have about a dozen different departments below that starting on row 2.

I thought about the format of the cells DD$2:DD$60000. They were formatted to General. I formatted the whole column to Number. No change, still result of 0.

Then, I got off refernecing a cell and went with TODAY(), still no change....result of 0.

Current Formula:
Code: 
=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'!A9)*('Project Task'!M$2:M$60000>=TODAY())*(DD$2:DD$60000),
('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'!A9)*('Project Task'!M$2:M$60000>=TODAY())*(DD$2:DD$60000))>0,1))-1



Dates cells are formatted as dates, numbers are formatted a number, others are formatted as text. Could it be data type format difference? I doubt it.

?


S
 
Upvote 0
Shdawson,

My suggestion is as follows:

Remove each of the parts of the formula and test.

For example:

Remove the formula part ('Project Task'!P$2:P$60000>0)*

the formula would look like this:

Code: 
=SUM(IF(FREQUENCY(
('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

Do a test.

Then, repeat the same process for the other parts.

Markmzz

<!-- / message -->
 
Upvote 0
Good suggestion.

Stripped to bear bones, still returning 0.


Current Formula:
Code: 
=SUM(IF(FREQUENCY(('Project Task'!A$2:A$60000='Release Chart - CALCUATIONS'!A9)*(DD$2:DD$60000),
('Project Task'!A$2:A$60000='Release Chart - CALCUATIONS'!A9)*(DD$2:DD$60000))>0,1))-1


It really seems like something in my installation of Excel cannot process the same as yours. I don't know what else it could be.

?


S
 
Last edited:
Upvote 0
Now, try only this formula:

=SUM(IF(FREQUENCY((DD$2:DD$60000),(DD$2:DD$60000))>0,1))-1

And tell me what it result.

Markmzz
 
Upvote 0
-1


If I understand things correctly, this population of the cells in column DD only happens in processing, like a cursor (temporary database table), yes?



Thanks,
SHD
 
Last edited:
Upvote 0
markmzz said:
Shdawson,

I'll try to explain using examples with comments and the help of Excel.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Help of Excel
<o:p></o:p>
<o:p></o:p>
FREQUENCY function
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>

Syntax <o:p></o:p>
<o:p></o:p>

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

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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Comments
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
FREQUENCY ignores blank cells and text.
<o:p></o:p>
<o:p></o:p>
Formulas that return arrays must be entered as array formulas.<o:p></o:p>

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
Click to expand...

The -1 is to remove the zero of the count of numbers.

Did you test the formula below?

=SUM(IF(FREQUENCY((DD$2:DD$60000),(DD$2:DD$60000))>0,1))-1

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Changing conditional formatting for Quarters/months
Replies
3
Views
494
felixstraube
F
M
  • Question Question
Populate Fee Schedule Based on Dates
Replies
5
Views
303
Cubist
Cubist
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
206
Tetra201
Tetra201
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
524
XLfriend777
XLfriend777
E
  • Question Question
Looking for VBA to help with data collection
Replies
0
Views
656
ExcelNewbie2009
E

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top