software

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:A40lt;4/1/6),(--(B10:B40lt;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!

Try this but note that blank columns will quot;passquot; the the test:

=SUMPRODUCT(--(A10:A40lt;--quot;2006/04/01quot;),(--(B10:B40lt;--quot;2006/04/01quot;)))

To ignore blanks:

=SUMPRODUCT(--(A10:A40lt;--quot;2006/04/01quot;),--(A10:A40lt;gt;quot;quot;),--(B10:B40lt;--quot;2006/04/01quot;),--(B10:B40lt;gt;quot;quot;))

HTH

quot;Manniquot; wrote:

gt; Column A has dates
gt; Column B has dates
gt;
gt; I need to count the number of times that there is an entry in column B
gt; within a date range (I am looking to do this by quarter) and an entry in
gt; column A within that same date range. The data needs to be imbedded into the
gt; formula rather than a seperate control cell. I have tried the following
gt; formula but am not getting the right answer:
gt;
gt; =SUMPRODUCT(--(A10:A40lt;4/1/6),(--(B10:B40lt;4/1/6)))
gt;
gt; The answer to by formula should be 2 but this is producing 24. Note that
gt; the data is on another worksheet.
gt;
gt; Thanks!

It works!!! Thank you!

quot;Toppersquot; wrote:

gt; Try this but note that blank columns will quot;passquot; the the test:
gt;
gt; =SUMPRODUCT(--(A10:A40lt;--quot;2006/04/01quot;),(--(B10:B40lt;--quot;2006/04/01quot;)))
gt;
gt; To ignore blanks:
gt;
gt; =SUMPRODUCT(--(A10:A40lt;--quot;2006/04/01quot;),--(A10:A40lt;gt;quot;quot;),--(B10:B40lt;--quot;2006/04/01quot;),--(B10:B40lt;gt;quot;quot;))
gt;
gt; HTH
gt;
gt; quot;Manniquot; wrote:
gt;
gt; gt; Column A has dates
gt; gt; Column B has dates
gt; gt;
gt; gt; I need to count the number of times that there is an entry in column B
gt; gt; within a date range (I am looking to do this by quarter) and an entry in
gt; gt; column A within that same date range. The data needs to be imbedded into the
gt; gt; formula rather than a seperate control cell. I have tried the following
gt; gt; formula but am not getting the right answer:
gt; gt;
gt; gt; =SUMPRODUCT(--(A10:A40lt;4/1/6),(--(B10:B40lt;4/1/6)))
gt; gt;
gt; gt; The answer to by formula should be 2 but this is producing 24. Note that
gt; gt; the data is on another worksheet.
gt; gt;
gt; gt; Thanks!