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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com