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

 

[T. Valko]

Biff,


--(TEXT(Range,"myyyy")="62011")



You seem to have nailed my need. Thank you. I have not 100% tested your formula, but I want to ask a question before you take off for the day.

A question about the "62011" part, please. I have 2 cells setup for my start-date and end-date. How can I reference a cell for the "62011" part? Do I need to setup a 3rd cell? If so, what should the contents of that cell be, please?

Thank you very, very much.


Kindly,
SHD

The "62011" stands for:

6 = month number for June
2011 = year number

If you have a cell that contains any date within June 2011:

A1 = 6/27/2011

Then:

--(TEXT(Range,"myyyy")=TEXT(A1,"myyyy"))

It'll even work if the range cells and the criteria cell includes a time like: 6/10/2011 12:15 PM.

 

[T. Valko]

Hmmmm, this formula is returning an error. Help file speaks to the TEXT function as follows:


TEXT
Converts a value to text in a specific number format.

Syntax
TEXT(value,format_text)


Perhaps I am missing something here. Please let me know how you have used the syntax you provided.


Thanks,
SHD

Here's an example:

Book1
	A	B
2	5/26/2011	8
3	6/8/2011	_
4	5/5/2011	_
5	6/28/2011	_
6	5/25/2011	_
7	7/25/2011	_
8	7/14/2011	_
9	6/11/2011	_
10	6/11/2011	_
11	6/14/2011	_
12	6/21/2011	_
13	7/30/2011	_
14	7/10/2011	_
15	5/23/2011	_
16	6/13/2011	_
17	7/20/2011	_
18	7/16/2011	_
19	7/4/2011	_
20	6/29/2011	_
Sheet1

Formula entered in B2:

=SUMPRODUCT(--(TEXT(A2:A20,"myyyy")="62011"))

 

[markmzz]

Biff's suggestion would be this (correct me if I'm wrong Biff):




<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: 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">TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3</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: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">RC</TD><TD style="BACKGROUND-COLOR: #ffff00">DTI</TD><TD style="BACKGROUND-COLOR: #ffff00">DTF</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">FILTER</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011 00:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/06/2011 23:59</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Col01</TD><TD>Col02</TD><TD>Col03</TD><TD>Col04</TD><TD>Col05</TD><TD>Col06</TD><TD>Col07</TD><TD>Col08</TD><TD>Col09</TD><TD>Col10</TD><TD>Col11</TD><TD>Col12</TD><TD>Col13</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">18/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4623</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">28/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4741</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">15/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">26/06/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>L1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A5:A$6000='Release Chart - CALCUATIONS'!A5),--(I5:I$6000="PRJ"),--(J5:J$6000="PRJ"),--(TEXT(L5:L6000,"maaaa")="62011"),--(TEXT(M5:M6000,"maaaa")="62011"))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[shdawson]

OK, I put together some test data to learn your formula. I under now, thanks.

Now, when I add your aspect to the current formula, I get a #VALUE! error. I have tried different types of entires to add to the SUMPRODUCT formula, all are returning the same #VALUE! error.


This works:

=SUMPRODUCT(--('Project Task'!A9:A$60000='Release Chart - CALCUATIONS'!A9),--('Project Task'!I9:I$60000="PRJ"),--('Project Task'!J9:J$60000="PRJ"))

But, when I add to it...I get the #VALUE! error.

=SUMPRODUCT(--('Project Task'!A9:A$60000='Release Chart - CALCUATIONS'!A9),--('Project Task'!I9:I$60000="PRJ"),--('Project Task'!J9:J$60000="PRJ"),--('Project Task'!M2:M$60000<=TODAY()))

I used TODAY() in your code with my test code, and all works fine. It seems that this may be a limitation of the SUMPRODUCT, where it is calcuating too many arrays (?) I struggle to believe that, as I hit F2, and place my cursor at the end to enter more info, and the yellow sytntax aide pops up, ready for array 5. I try the show calcuation steps and the machine grinds and does not ever get there. Maybe hardware limitation?

Hmmmm.......anyone seen this before?


Thanks,
SHD

 

[shdawson]

Something is wrong with the date field. I added some other array info to the SUMPRODUCT formula, all works. It is unique to the data in the column with the dates.

I add the array...
--('Project Task'!M$2:M$60000<='Release Chart - LOG'!J$4)

and the #VALUE! comes.


NOTE: 'Release Chart - LOG'!J$4 is one of my dates (FINISH DATE)



I add the array...
--('Project Task'!P2:P$60000>0)

...without the --('Project Task'!M$2:M$60000<='Release Chart - LOG'!J$4)
...and all is well.



Hmmmm.....I have the cells 'Project Task'!M$2:M$60000 formatted as date. Have the whole column formatted as date for that matter.

Again, trying to use the SHOW CALCUATION part of error detection, and it grinds the machine to a halt. I see lots of 0,0,0,0,0,0,0,0, in what I can see of the SHOW CALCULATION part.

I see the ERROR CHECKING button telling me, "A value used in the formula is of the wrong data type". So, that seems like that is it. This seems like an easy fix. Perhaps an enchancemnt to Biff's formula?



Thanks,
SHD

 

[markmzz]

Probably you have blank cells in a date column.

Here, your formula is 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: 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">TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">RC</TD><TD style="BACKGROUND-COLOR: #ffff00">DTI</TD><TD style="BACKGROUND-COLOR: #ffff00">DTF</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">FILTER</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011 00:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/06/2011 23:59</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Col01</TD><TD>Col02</TD><TD>Col03</TD><TD>Col04</TD><TD>Col05</TD><TD>Col06</TD><TD>Col07</TD><TD>Col08</TD><TD>Col09</TD><TD>Col10</TD><TD>Col11</TD><TD>Col12</TD><TD>Col13</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">55</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">19/05/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">05/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">525</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">09/04/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">20/04/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">18/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3138</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/05/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">19/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4719</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">14/04/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/04/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5197</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/05/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">22/05/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5936</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">17/04/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/05/2011</TD></TR></TBODY></TABLE>


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


Markmzz

 

[shdawson]

Really! Wow, OK, well....I know that is the case, as I have lots of blanks in column M. I have about 27,000 entires, but the number goes up and down.

How can I deal with the blanks? It would be doable to measure the excact cell range, but some what of a hassle. If I have to, I have to.

Please advise.


Thank you very, very much.


Kindly,
SHD

 

[markmzz]

I found an error in your formula.

Look at M2 in your formula (It should be M9):

=SUMPRODUCT(--('Project Task'!A9:A$60000='Release Chart - CALCUATIONS'!A9),--('Project Task'!I9:I$60000="PRJ"),--('Project Task'!J9:J$60000="PRJ"),--('Project Task'!M2:M$60000<=TODAY()))

Markmzz

 

[shdawson]

Markmzz,


You nailed it!!! The dimensions of the array were not plumb/square, across the board. Had the starter rows off here and there, but the ending rows were flush.

All works as expected now. I will look at this again tomorrow, as it is late and I may have 1 other qualifer.

Thank you all very, very much!


Kindest Regards,
SHD

 

[markmzz]

Ok.

Do more tests tomorrow and let us know if it worked.

Markmzz