Countif month & year
 

I have 1000+ records over 4 years & want to count how many for each month &
year ie May 2005. Records have specific days 4 May 2005
I can use :
=SUMPRODUCT(--(YEAR(c2:c1000)=2005),--(MONTH(c2:c1000)=4)) to pick up
individual month year, but I want to make life easier & not type in each year
& month (4 years x 12 months & growing!)
Any advice

Saintsman

Countif month & year
 

Hi

Ideally, use a Pivot Table and Group the Date filed by Year and Month.
For help on setting up Pivot Tables
Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx

If you don't want to use a Pivot Table, use a second sheet and set up a
matrix with 1 to 12 in cells B1:M1
In A2 enter your first year, A3 your next year etc.

In B2
SUMPRODUCT(--(YEAR(Sheet1!$C$2:$C$1000)=$A2),--(MONTH(Sheet1!$C$2:$C$1000)=B$1))
Copy across and down as required.

--
Regards

Roger Govier


"Saintsman" wrote in message
...
I have 1000+ records over 4 years & want to count how many for each
month &
year ie May 2005. Records have specific days 4 May 2005
I can use :
=SUMPRODUCT(--(YEAR(c2:c1000)=2005),--(MONTH(c2:c1000)=4)) to pick up
individual month year, but I want to make life easier & not type in
each year
& month (4 years x 12 months & growing!)
Any advice

Saintsman

Countif month & year
 

With
A1: MyDate
A2:A400 containing dates

Try something like this:
Select A1:A400

Then...From the Excel main menu:
<data<pivot table
Check: Microsoft Excel list..........Click [Next]
(A1:A400 is already selected).....Click [Next]
Click the [Layout] button
ROW: Drag the MyDate field here
DATA: Drag the MyDate field here, too
Click [OK]
Click [Finish]

Almost done.....
Right-Click on the MyDate column heading
<Group and show detail<Group
Select MONTH and YEAR.......Click [OK]

Excel will automatically group the dates by YEAR then by MONTH.
The end result will look like this:
Count of MyDate
Years MyDate Total
2007 Jan 11
Feb 9
Mar 10
Apr 10
May 11

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Saintsman" wrote:

I have 1000+ records over 4 years & want to count how many for each month &
year ie May 2005. Records have specific days 4 May 2005
I can use :
=SUMPRODUCT(--(YEAR(c2:c1000)=2005),--(MONTH(c2:c1000)=4)) to pick up
individual month year, but I want to make life easier & not type in each year
& month (4 years x 12 months & growing!)
Any advice

Saintsman