Hi

I have a column of dates covering several years. I want to total the dates
as # of events per month per year.

Example

date range my result should be
01/dd/yy01 Jan-01 = 2
01/dd/yy01 Jan-03 = 1
01/dd/yy03

and so on

Also, what does the -- in the sumproduct formulae mean?

Thank you
Dave

This is an array formula - commit with Shift-Ctrl_enter

=SUMPRODUCT(--(MONTH(A23:A163)=Month),--(YEAR(A23:A163)=Year))

"Dave" wrote:

Hi

I have a column of dates covering several years. I want to total the dates
as # of events per month per year.

Example

date range my result should be
01/dd/yy01 Jan-01 = 2
01/dd/yy01 Jan-03 = 1
01/dd/yy03

and so on

Also, what does the -- in the sumproduct formulae mean?

Thank you
Dave

yOU NEED TO USE A COUNTIF FORMULA AS FOLLOWS:

=COUNTIF(CELL RANGE,"=01/2001")
=COUNTIF(CELL RANGE,"=01/2003")

YOUR CELL RANGE IS THE CO,UMS CONTAINING YOUR DATES

"Dave" wrote:

Hi

I have a column of dates covering several years. I want to total the dates
as # of events per month per year.

Example

date range my result should be
01/dd/yy01 Jan-01 = 2
01/dd/yy01 Jan-03 = 1
01/dd/yy03

and so on

Also, what does the -- in the sumproduct formulae mean?

Thank you
Dave

Where you have MONTH(A23.A163), that column is filled with various dates that
are formatted mm-yy, e.g., 01/01/01, 01/19/01 results in Jan-01, Jan-01.

Where you have =Month, I have a cell entry referring to a column formatted
as Jan-01 (1/1/01, followed by Feb-01 (2/1/01), and so on through Dec-07.

I'm getting zeros when I use the formula you suggested.

Thanks

Dave

"Duke Carey" wrote:

This is an array formula - commit with Shift-Ctrl_enter

=SUMPRODUCT(--(MONTH(A23:A163)=Month),--(YEAR(A23:A163)=Year))

"Dave" wrote:

Hi

I have a column of dates covering several years. I want to total the dates
as # of events per month per year.

Example

date range my result should be
01/dd/yy01 Jan-01 = 2
01/dd/yy01 Jan-03 = 1
01/dd/yy03

and so on

Also, what does the -- in the sumproduct formulae mean?

Thank you
Dave

If the data really are DATE values & not text values that look like dates,
then the formula will work

=SUMPRODUCT(--(MONTH(A23:A163)=1),--(YEAR(A23:A163)=2003))

will tell you the count of items in Jan-03.




"Dave" wrote:

Where you have MONTH(A23.A163), that column is filled with various dates that
are formatted mm-yy, e.g., 01/01/01, 01/19/01 results in Jan-01, Jan-01.

Where you have =Month, I have a cell entry referring to a column formatted
as Jan-01 (1/1/01, followed by Feb-01 (2/1/01), and so on through Dec-07.

I'm getting zeros when I use the formula you suggested.

Thanks

Dave

"Duke Carey" wrote:

This is an array formula - commit with Shift-Ctrl_enter

=SUMPRODUCT(--(MONTH(A23:A163)=Month),--(YEAR(A23:A163)=Year))

"Dave" wrote:

Hi

I have a column of dates covering several years. I want to total the dates
as # of events per month per year.

Example

date range my result should be
01/dd/yy01 Jan-01 = 2
01/dd/yy01 Jan-03 = 1
01/dd/yy03

and so on

Also, what does the -- in the sumproduct formulae mean?

Thank you
Dave