Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif help
I have a quick question. i am trying to count the cells with a date range. i
ceonverted the date to a value and tried this but it is not counting correctly. =SUM(COUNTIF(D6:D505,(AND("=39995","<=40026")))) There are 9 with the date 7-31-09 and 15 with 8-6-09. So it comes up with zero - I know beacsue with the and it is true or false. How can I get it to count between the 2 dates of = 7-1-09 and < 8-1-09? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif help
Use DATEVALUE()
Using COUNTIF (all in one line) =COUNTIF(D6:D505,"="&DATEVALUE("01-jul-2009"))-COUNTIF(D6:D505,"="&DATEVALUE("01-AUG-2009")) Using SUMPRODUCT =SUMPRODUCT(--(--TEXT(D6:D505,"m")=7)) If this post helps click Yes --------------- Jacob Skaria "jtfalk" wrote: I have a quick question. i am trying to count the cells with a date range. i ceonverted the date to a value and tried this but it is not counting correctly. =SUM(COUNTIF(D6:D505,(AND("=39995","<=40026")))) There are 9 with the date 7-31-09 and 15 with 8-6-09. So it comes up with zero - I know beacsue with the and it is true or false. How can I get it to count between the 2 dates of = 7-1-09 and < 8-1-09? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif help
Using SUMPRODUCT() with the year..
=SUMPRODUCT(--(TEXT(D6:D505,"mmyyyy")="072009")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Use DATEVALUE() Using COUNTIF (all in one line) =COUNTIF(D6:D505,"="&DATEVALUE("01-jul-2009"))-COUNTIF(D6:D505,"="&DATEVALUE("01-AUG-2009")) Using SUMPRODUCT =SUMPRODUCT(--(--TEXT(D6:D505,"m")=7)) If this post helps click Yes --------------- Jacob Skaria "jtfalk" wrote: I have a quick question. i am trying to count the cells with a date range. i ceonverted the date to a value and tried this but it is not counting correctly. =SUM(COUNTIF(D6:D505,(AND("=39995","<=40026")))) There are 9 with the date 7-31-09 and 15 with 8-6-09. So it comes up with zero - I know beacsue with the and it is true or false. How can I get it to count between the 2 dates of = 7-1-09 and < 8-1-09? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif help
Sumproduct is one of my favorite functions! It can handle many different
arguments; it is very powerful! http://www.lqnet.com/Excel/sumproduct.aspx http://www.contextures.com/xlFunctio...tml#SumProduct http://www.contextures.com/xlFunctio...tml#SumProduct Learn it, and you will find many uses for it!! HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Using SUMPRODUCT() with the year.. =SUMPRODUCT(--(TEXT(D6:D505,"mmyyyy")="072009")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Use DATEVALUE() Using COUNTIF (all in one line) =COUNTIF(D6:D505,"="&DATEVALUE("01-jul-2009"))-COUNTIF(D6:D505,"="&DATEVALUE("01-AUG-2009")) Using SUMPRODUCT =SUMPRODUCT(--(--TEXT(D6:D505,"m")=7)) If this post helps click Yes --------------- Jacob Skaria "jtfalk" wrote: I have a quick question. i am trying to count the cells with a date range. i ceonverted the date to a value and tried this but it is not counting correctly. =SUM(COUNTIF(D6:D505,(AND("=39995","<=40026")))) There are 9 with the date 7-31-09 and 15 with 8-6-09. So it comes up with zero - I know beacsue with the and it is true or false. How can I get it to count between the 2 dates of = 7-1-09 and < 8-1-09? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using COUNTIF | New Users to Excel | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |