Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |