Count Feature with dates **HELP**

[lsreleford]

Hi All - I'm wondering if there is some type of formula that will count the dates in a column and spit some numbers out. For instance, I have multiple dates in column A rows 1 - 100. I would like the total number of Oct dates in B1, Sept in B2 and the total number of Nov dates in B3. How can I make this happen?

 

[T. Valko]

Hi All - I'm wondering if there is some type of formula that will count the dates in a column and spit some numbers out. For instance, I have multiple dates in column A rows 1 - 100. I would like the total number of Oct dates in B1, Sept in B2 and the total number of Nov dates in B3. How can I make this happen?

Try these...

For Oct dates in B1:

=SUMPRODUCT(--(MONTH(A1:A100)=10))

For Sept dates in B2

=SUMPRODUCT(--(MONTH(A1:A100)=9))

For Nov dates in B3

=SUMPRODUCT(--(MONTH(A1:A100)=11))

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--(TEXT(A1:A100,"mmm")="Sep"))

 

[lsreleford]

Thanks Andrew, that worked. Now, I need it to count (without filtering) how many cells have a date in it period. There are cells between A1 and A100 that have a dash (-) when there is no date, but I need to know how many cells have a date period.

 

[T. Valko]

Thanks Andrew, that worked. Now, I need it to count (without filtering) how many cells have a date in it period. There are cells between A1 and A100 that have a dash (-) when there is no date, but I need to know how many cells have a date period.

Try this...

=COUNT(A1:A100)

 

[T. Valko]

There are cells between A1 and A100 that have a dash (-) when there is no date

You didn't mention that in your first post so the formulas I suggested won't work in that case.

 

[lsreleford]

Oh no T. Valko......my 2nd question is seperate. I'm just using A1 - A100 as examples.

When I use the COUNT(A1:A100) formula, it still counts the cells with a dash. I want it to only give me a count of the number of cells with a date in it.

 

[T. Valko]

Oh no T. Valko......my 2nd question is seperate. I'm just using A1 - A100 as examples.

When I use the COUNT(A1:A100) formula, it still counts the cells with a dash. I want it to only give me a count of the number of cells with a date in it.

The dash must be a formatting display for 0. The COUNT function will not count literal dashes.

Maybe this...

=COUNTIF(A1:A100,">0")

 

[Aladin Akyurek]

Hi All - I'm wondering if there is some type of formula that will count the dates in a column and spit some numbers out. For instance, I have multiple dates in column A rows 1 - 100. I would like the total number of Oct dates in B1, Sept in B2 and the total number of Nov dates in B3. How can I make this happen?

Thanks Andrew, that worked. Now, I need it to count (without filtering) how many cells have a date in it period. There are cells between A1 and A100 that have a dash (-) when there is no date, but I need to know how many cells have a date period.

Control+shift+enter, not just enter, and copy down...

=SUM(IF(ISNUMBER($A$1:$A$100),
  IF($A$1:$A$100-DAY($A$1)+1=DATE(2012,10+ROWS($B$1:B1)-1,1),1)),1)

Is this what you intended?

 

[lsreleford]

So I'm using =SUMPRODUCT(--(TEXT(A1:A100,"mmm")="Nov")) to calculate how many cells in a the column have a November date. The problem is, how do I account for the year? The column includes multiple years. I tried "Nov2012" but that didn't work.