Formula counts incorrectly
 

Help please.
Column K contains dates and blank cells. I would like to count how many
of these dates fall within a given date range. For example, in the
first week in May. Column K contains these dates. 5/2/2005, 5/3/2005,
5/5/2005, 5/5/2005. I would like to count this as 4 dates within the
first week of May. I created this formula but the output is 3.

=SUMPRODUCT(--(ECNT!F2:F515=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515)))

I assume that it is only counting 5/5/2005 one time?

I changed the dates in the formula to check a second 2nd date range
(5/9 to 5/13). Column K contains only one date within that range
5/13/2005. However this formula returns 2.

Could someone please modify this formula to work the way I want it to?


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=379171

It workes fine for me:.

=SUMPRODUCT((A2:A10=DATE(2005,5,2))*(A2:A10<=DATE (2005,5,6))*(ISNUMBER(B2:B10)))
--- 4

Hope it helped
Ola Sandström


Pictu
http://www.excelforum.com/attachment...tid=3501&stc=1


+-------------------------------------------------------------------+
|Filename: Clipboard01.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3501 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=379171

Quick question your write up talks about column K only
your equation shows Column F
What is the is number section for?

"was" wrote:


Help please.
Column K contains dates and blank cells. I would like to count how many
of these dates fall within a given date range. For example, in the
first week in May. Column K contains these dates. 5/2/2005, 5/3/2005,
5/5/2005, 5/5/2005. I would like to count this as 4 dates within the
first week of May. I created this formula but the output is 3.

=SUMPRODUCT(--(ECNT!F2:F515=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515)))

I assume that it is only counting 5/5/2005 one time?

I changed the dates in the formula to check a second 2nd date range
(5/9 to 5/13). Column K contains only one date within that range
5/13/2005. However this formula returns 2.

Could someone please modify this formula to work the way I want it to?


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=379171