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?
 

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
Joined
May 9, 2009
Messages
16,623
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))
 

lsreleford

New Member
Joined
May 8, 2012
Messages
37
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
Joined
May 9, 2009
Messages
16,623

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)
 

lsreleford

New Member
Joined
May 8, 2012
Messages
37

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
Joined
May 9, 2009
Messages
16,623
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
Joined
Feb 14, 2002
Messages
85,209
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
Joined
May 8, 2012
Messages
37
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.
 

Forum statistics

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

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
Top