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]

Oh, yes....sorry to not be clear in my response. I put that formula into a worksheet, and the result was -1.


Thanks,
SHD

 

[markmzz]

For help you understand what Excel do with the data of column DD.

The Excel will only make the count of records from your list to a single instance of each number in column DD.

As in post #53.

Only the total of 6 differents numbers.

I think that the problem is in the column DD.

If you wanted I can give you my workbook for tests.

Give me your email for private message and I will send it to you.

Markmzz<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[shdawson]

Column DD was the problem.

Funny how this machine mandates the full path to a cell, even for on-worksheet references. Things were problematic until that was understood. Finally, referencing the column with the unique identifier for the measurement and not an empty column as a temp-area. Again, this was a lack of understanding on my part as to how this array played into your formula.

Wow, what a formula. I have tons of usages for this. You are quite a programmer, sir. Well done!

Thank you very, very much for your time and assistance.


Kindly,
SHD

 

[markmzz]

Column DD was the problem.

Funny how this machine mandates the full path to a cell, even for on-worksheet references. Things were problematic until that was understood. Finally, referencing the column with the unique identifier for the measurement and not an empty column as a temp-area. Again, this was a lack of understanding on my part as to how this array played into your formula.

Wow, what a formula. I have tons of usages for this. You are quite a programmer, sir. Well done!

Thank you very, very much for your time and assistance.


Kindly,
SHD

Shdawson,

We both did a good job.

Thank you for your feedback.

And until a next opportunity.

Markmzz<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>