Count Feature with dates **HELP**

lsreleford

New Member
Joined
May 8, 2012
Messages
37
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?
 
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...
 
Upvote 0
The solution in #9 uses column B so it kinda jack's up everything as there is other data being tracked in column B
 
Upvote 0
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"))
 
Upvote 0
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:

Rich (BB code):
=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...
Rich (BB code):
IF($A$1:$A$100-DAY($A$1)+1=DATE(2012,11,1)
Or...
Rich (BB code):
IF($A$1:$A$100-DAY($A$1)+1="1-Nov-12"+0
Or
Rich (BB code):
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top