Counting Dates

gjohnson6 · May 22nd, 2002, 12:35 PM

I have a range of cells each containing a date (day-mo-yr). I'd like to count the number of cells with a January date.

Aladin Akyurek · May 22nd, 2002, 12:40 PM

Quote:

On 2002-05-22 11:35, gjohnson6 wrote:
I have a range of cells each containing a date (day-mo-yr). I'd like to count the number of cells with a January date.

=SUMPRODUCT((MONTH(A2:A100)=1)+0)

IML · May 22nd, 2002, 12:42 PM

You could use
=SUMPRODUCT((MONTH(A1:A10)=1)*ISNUMBER(A1:A10))

for January. substitute the month number for the =1 representing January. Note this not year specific.

Post Script -
I missed Aladin's post. I believe these formulas will behave identically over a range with no blanks. I threw in the isnumber part in case you do have blanks, which would only throw you off when calculating January. good luck

[ This Message was edited by: IML on 2002-05-22 11:51 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 12:35 PM	#1
gjohnson6 New Member Join Date: May 2002 Posts: 1	I have a range of cells each containing a date (day-mo-yr). I'd like to count the number of cells with a January date.

May 22nd, 2002, 12:42 PM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	You could use =SUMPRODUCT((MONTH(A1:A10)=1)*ISNUMBER(A1:A10)) for January. substitute the month number for the =1 representing January. Note this not year specific. Post Script - I missed Aladin's post. I believe these formulas will behave identically over a range with no blanks. I threw in the isnumber part in case you do have blanks, which would only throw you off when calculating January. good luck [ This Message was edited by: IML on 2002-05-22 11:51 ]