Count Dates between Dates exclude Text
 

Excel2003 ... Col "H" contains Dates (mm/dd/yy), Text comments & Empty Cells.

I wish to count dates by Qtr (excl Text) ... How many dates occurred between?

01/01/09 to 03/31/09
04/01/09 to 06/30/09
07/01/09 to 09/30/09
10/01/09 to 12/31/09

Sumproduct keeps giving me the "NUM" error ... Countif keeps counting the
Text.

Above said ... I am fully aware this is a short-coming on my part ... :)

Thanks for a solution ... Kha

Count Dates between Dates exclude Text
 

try this array formula
Array enter (See below) and drag down 3 rows to get the 4 quarters

=COUNT(IF(INT((MONTH(H1:H1000)-1)/3)+1=ROW(A1),IF(H1:H1000<"",1)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Ken" wrote:

Excel2003 ... Col "H" contains Dates (mm/dd/yy), Text comments & Empty Cells.

I wish to count dates by Qtr (excl Text) ... How many dates occurred between?

01/01/09 to 03/31/09
04/01/09 to 06/30/09
07/01/09 to 09/30/09
10/01/09 to 12/31/09

Sumproduct keeps giving me the "NUM" error ... Countif keeps counting the
Text.

Above said ... I am fully aware this is a short-coming on my part ... :)

Thanks for a solution ... Kha

Count Dates between Dates exclude Text
 

=sumproduct(--(A1:A100=datevalue("1/1/09")),--(A1:A100<=datevalue("3/31/09")))

"Ken" wrote:

Excel2003 ... Col "H" contains Dates (mm/dd/yy), Text comments & Empty Cells.

I wish to count dates by Qtr (excl Text) ... How many dates occurred between?

01/01/09 to 03/31/09
04/01/09 to 06/30/09
07/01/09 to 09/30/09
10/01/09 to 12/31/09

Sumproduct keeps giving me the "NUM" error ... Countif keeps counting the
Text.

Above said ... I am fully aware this is a short-coming on my part ... :)

Thanks for a solution ... Kha

Count Dates between Dates exclude Text
 

Joel ... (Hi)

I finally got this with Sumproduct ... I forgot that you can't use Col Ref
when using this great function & it took me awhile to figure it out ...

Thanks for supporting these boards ... Kha

"joel" wrote:

=sumproduct(--(A1:A100=datevalue("1/1/09")),--(A1:A100<=datevalue("3/31/09")))

"Ken" wrote:

Excel2003 ... Col "H" contains Dates (mm/dd/yy), Text comments & Empty Cells.

I wish to count dates by Qtr (excl Text) ... How many dates occurred between?

01/01/09 to 03/31/09
04/01/09 to 06/30/09
07/01/09 to 09/30/09
10/01/09 to 12/31/09

Sumproduct keeps giving me the "NUM" error ... Countif keeps counting the
Text.

Above said ... I am fully aware this is a short-coming on my part ... :)

Thanks for a solution ... Kha