Result Based on Date Criteria
 

Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

Result Based on Date Criteria
 

Hi,

This is a little verbose but it will give you the month and year of a date...
you can then do your analysis on this column.

=LOOKUP(MONTH(A15),{1,2,3,4,5,6,7,8,9,10,11,12},{" January","February","March",
"April","May","June","July","August","September"," October","November",
"December"})&" "&YEAR(A15)

If you don't care about having January then simple =month(A15)&" "&YEAR(A15)
will work.

HTH

Simon

pobm62 wrote:
Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via http://www.officekb.com

Result Based on Date Criteria
 

One way to count the January 2006 dates:

=SUMPRODUCT(--(TEXT(H7:H129,"yyyymm")="200601"))

You may want to learn more about pivottables. You can group by month and year
and get a count for all your data pretty quickly.

pobm62 wrote:

Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

--

Dave Peterson

Result Based on Date Criteria
 

ps.

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Dave Peterson wrote:

One way to count the January 2006 dates:

=SUMPRODUCT(--(TEXT(H7:H129,"yyyymm")="200601"))

You may want to learn more about pivottables. You can group by month and year
and get a count for all your data pretty quickly.

pobm62 wrote:

Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

--

Dave Peterson

--

Dave Peterson

Result Based on Date Criteria
 

You could use a pivot table to accomplish this. One advantage is that pivot
tables have built functionality when grouping date fields that will group
records by month and year.
Gary

"pobm62" wrote:

Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

Result Based on Date Criteria
 

Fantastic. Thanks Dave that worked as a short term measure - I don't want to
have to keep changing the year date for the results.
Will now look and learn about pivot tables as has been suggested.

pobm62

"Dave Peterson" wrote:

One way to count the January 2006 dates:

=SUMPRODUCT(--(TEXT(H7:H129,"yyyymm")="200601"))

You may want to learn more about pivottables. You can group by month and year
and get a count for all your data pretty quickly.

pobm62 wrote:

Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

--

Dave Peterson

Result Based on Date Criteria
 

Some links...

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

pobm62 wrote:

Fantastic. Thanks Dave that worked as a short term measure - I don't want to
have to keep changing the year date for the results.
Will now look and learn about pivot tables as has been suggested.

pobm62

"Dave Peterson" wrote:

One way to count the January 2006 dates:

=SUMPRODUCT(--(TEXT(H7:H129,"yyyymm")="200601"))

You may want to learn more about pivottables. You can group by month and year
and get a count for all your data pretty quickly.

pobm62 wrote:

Hi,

This is my first post but I want to thank everyone who has posted before me
as I have been able to learn so much from previous questions.

I have a column in a spreadsheet (H7:H129) that covers a range of dates -
and these dates can go across more than one year. I have learnt how to count
the number of times a certain month appears but now I want to count the
months dependent on the year, eg count how many entries there are for July
2006, September 2006, November 2006 etc.

This spreadsheet will be used for years into the future so I want it to
automatically read the year from the worksheet. There is already a cell in
the worksheet where the date is manually inserted at the start of the month.
It displays as mmm-yy.

Any assistance will be greatly appreciated.

Thanks in advance.

--

Dave Peterson


--

Dave Peterson