# Count Feature with dates **HELP**

#### lsreleford

##### New Member
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?

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### T. Valko

##### Well-known Member
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

##### MrExcel MVP
Example:

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

#### lsreleford

##### New Member
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

##### Well-known Member

ADVERTISEMENT

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

##### Well-known Member
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

##### New Member

ADVERTISEMENT

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

##### Well-known Member
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

##### MrExcel MVP
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...
Rich (BB code):
``````=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

##### New Member
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.

Replies
3
Views
55
Replies
0
Views
124
Replies
29
Views
192
Replies
31
Views
449
Replies
2
Views
39

Threads
1,136,323
Messages
5,675,089
Members
419,549
Latest member
EliteBeat

### 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

### 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