Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula
I have a list with the dates in column A. On sheet 2 I wan't to count the
number of entries between 2 dates. This is what I have tried so far: =COUNTIF(A:A,"<01/01/2005,31/01/2005") I want it to count how many entries are in March. Any Ideas, Thanks |
#2
|
|||
|
|||
=SUMPRODUCT(--(A1:A1000=--"2005/03/01"),--(A1:A1000<=--"2005/03/31"))
will count between two dates, but if you just want March you can use =SUMPRODUCT(--(MONTH(A1:A1000)=3)) 0r =SUMPRODUCT(--(TEXT(A1:A1000,"mmm")="Mar")) Note, SUMPRODUCT does not work on a whole column, but a defined range. -- HTH Bob Phillips "canna" wrote in message ... I have a list with the dates in column A. On sheet 2 I wan't to count the number of entries between 2 dates. This is what I have tried so far: =COUNTIF(A:A,"<01/01/2005,31/01/2005") I want it to count how many entries are in March. Any Ideas, Thanks |
#3
|
|||
|
|||
Thanks bj
"bj" wrote: try =countif(A:A,"<="&datevalue(3/31/2005))-countif(A:A,"="&datevalue(3/1/2005)) or you could set cells = meginning and end dates (B1,B2) =countif(A:A,"<="&B1)-countif(A:A,"="&B2) "canna" wrote: I have a list with the dates in column A. On sheet 2 I wan't to count the number of entries between 2 dates. This is what I have tried so far: =COUNTIF(A:A,"<01/01/2005,31/01/2005") I want it to count how many entries are in March. Any Ideas, Thanks |
#4
|
|||
|
|||
try
=countif(A:A,"<="&datevalue(3/31/2005))-countif(A:A,"="&datevalue(3/1/2005)) or you could set cells = meginning and end dates (B1,B2) =countif(A:A,"<="&B1)-countif(A:A,"="&B2) "canna" wrote: I have a list with the dates in column A. On sheet 2 I wan't to count the number of entries between 2 dates. This is what I have tried so far: =COUNTIF(A:A,"<01/01/2005,31/01/2005") I want it to count how many entries are in March. Any Ideas, Thanks |
#5
|
|||
|
|||
Thanks Bob, your a star.
"Bob Phillips" wrote: =SUMPRODUCT(--(A1:A1000=--"2005/03/01"),--(A1:A1000<=--"2005/03/31")) will count between two dates, but if you just want March you can use =SUMPRODUCT(--(MONTH(A1:A1000)=3)) 0r =SUMPRODUCT(--(TEXT(A1:A1000,"mmm")="Mar")) Note, SUMPRODUCT does not work on a whole column, but a defined range. -- HTH Bob Phillips "canna" wrote in message ... I have a list with the dates in column A. On sheet 2 I wan't to count the number of entries between 2 dates. This is what I have tried so far: =COUNTIF(A:A,"<01/01/2005,31/01/2005") I want it to count how many entries are in March. Any Ideas, Thanks |
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 | |||
put formula results into a different cell if it is empty | 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) |