Counting data columns with dates
 

Column A has dates
Column B has dates

I need to count the number of times that there is an entry in column B
within a date range (I am looking to do this by quarter) and an entry in
column A within that same date range. The data needs to be imbedded into the
formula rather than a seperate control cell. I have tried the following
formula but am not getting the right answer:

=SUMPRODUCT(--(A10:A40<4/1/6),(--(B10:B40<4/1/6)))

The answer to by formula should be 2 but this is producing 24. Note that
the data is on another worksheet.

Thanks!

Counting data columns with dates
 

Try this but note that blank columns will "pass" the the test:

=SUMPRODUCT(--(A10:A40<--"2006/04/01"),(--(B10:B40<--"2006/04/01")))

To ignore blanks:

=SUMPRODUCT(--(A10:A40<--"2006/04/01"),--(A10:A40<""),--(B10:B40<--"2006/04/01"),--(B10:B40<""))

HTH

"Manni" wrote:

Column A has dates
Column B has dates

I need to count the number of times that there is an entry in column B
within a date range (I am looking to do this by quarter) and an entry in
column A within that same date range. The data needs to be imbedded into the
formula rather than a seperate control cell. I have tried the following
formula but am not getting the right answer:

=SUMPRODUCT(--(A10:A40<4/1/6),(--(B10:B40<4/1/6)))

The answer to by formula should be 2 but this is producing 24. Note that
the data is on another worksheet.

Thanks!

Counting data columns with dates
 

It works!!! Thank you!

"Toppers" wrote:

Try this but note that blank columns will "pass" the the test:

=SUMPRODUCT(--(A10:A40<--"2006/04/01"),(--(B10:B40<--"2006/04/01")))

To ignore blanks:

=SUMPRODUCT(--(A10:A40<--"2006/04/01"),--(A10:A40<""),--(B10:B40<--"2006/04/01"),--(B10:B40<""))

HTH

"Manni" wrote:

Column A has dates
Column B has dates

I need to count the number of times that there is an entry in column B
within a date range (I am looking to do this by quarter) and an entry in
column A within that same date range. The data needs to be imbedded into the
formula rather than a seperate control cell. I have tried the following
formula but am not getting the right answer:

=SUMPRODUCT(--(A10:A40<4/1/6),(--(B10:B40<4/1/6)))

The answer to by formula should be 2 but this is producing 24. Note that
the data is on another worksheet.

Thanks!