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?

 

[Aladin Akyurek]

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.

See Post #9 which makes exactly that year point...

 

[lsreleford]

The solution in #9 uses column B so it kinda jack's up everything as there is other data being tracked in column B

 

[T. Valko]

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.

Like this...

=SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Nov2012"))

 

[Andrew Poulsom]

Like this?

=SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Nov2012"))

 

[Aladin Akyurek]

The solution in #9 uses column B so it kinda jack's up everything as there is other data being tracked in column B

It doesn't use anything from column B literally... Let's say column F is free and you want a running month/year average:

In F1 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($F$1:F1)-1,1),1)),1)

Let's say that the month/year of interest is: November 2012. The kernel idea is:

Either...

IF($A$1:$A$100-DAY($A$1)+1=DATE(2012,11,1)

Or...

IF($A$1:$A$100-DAY($A$1)+1="1-Nov-12"+0

Or

IF(TEXT($A$1:$A$100,"mmmyy")=TEXT("1-Nov-12","mmmyy")

The latter is not international, i.e., not readable in Dutch for example.

 

[lsreleford]

This Worked!! Thank you

 

[lsreleford]

Like this?

=SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Nov2012"))

This worked!! Thank you so much!