counting months

[dzorug]

hi, i'm using excel 97, and i have a column with dd-mmm-yy dates. i want
to count how many cells are in january, how many in february etc. how
do i do that? any tips much appreciated.
-dzorug

 

[Paul]

hi, i'm using excel 97, and i have a column with dd-mmm-yy dates. i want
to count how many cells are in january, how many in february etc. how
do i do that? any tips much appreciated.
-dzorug

One way:
=SUMPRODUCT(--(MONTH(A1:A50)=1))
will count those in January. Change =1 to =2 for Feb., etc.
Note, however, that any blank cells will be counted in January.

 

[Stephen Bullen]

Hi Dzorug,

hi, i'm using excel 97, and i have a column with dd-mmm-yy dates. i want
to count how many cells are in january, how many in february etc. how
do i do that? any tips much appreciated.

Two ways spring to mind:

One is to create a pivot table using that range as the input source,
adding the Date column to the row area and Count of Date to the Data area,
then group the Date in the row area by month (and year if you need that
too).

The second is to use the formula:

=SUMPRODUCT(N(MONTH(A1:A20)=1))

and change the 1 for month number you're interested in.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[dzorug]

thanks much. so how does that function work? how come blank cells are
counted, and what does N do?
-dzorug

 

[Peo Sjoblom]

It does that because excel sees empty cells as 0 and 0 date in excel was in
January 1900, so you would need this amendment for the formula to work

=SUMPRODUCT(--(MONTH(A1:A50)=1),--ISNUMBER(A1:A50))

--

Regards,

Peo Sjoblom

thanks much. so how does that function work? how come blank cells are
counted, and what does N do?
-dzorug


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Stephen Bullen]

Hi Dzorug,

what does N do?

The function N() converts booleans to numbers, the same as the
double-negative that other people use.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[dzorug]

thanks