counting the number of events in a year

[Guest]

I have a list of fire events that I need to sort by year. The date field for
each event is formated as mm-dd-yyyy. I can't figure out how to restrict the
count function to just pick up the year. The data spans 40 years so I don't
want to have to write out 40 different date range functions. Any
suggestions? I thought this would be easier....

 

[Guest]

Let's say your year data is in column A. Try something like this

=SUMPRODUCT(--(YEAR(Sheet1!$A:$A)=2007))

If you want to count a specific event in the year, do this

=SUMPRODUCT(--(YEAR(Sheet1!$A:$A)=2007),--(Sheet1!$B:$B = "Event"))

 

[Ron Coderre]

Perhaps a Pivot Table?

With
Col_A containing dates, with A1: EventDate
Col_B containing events, with B1: Event

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW: Drag the EventDate field here
COLUMN: (Leave this field blank)
DATA: Drag the Event field field here
If it doesn't list as Count of Event...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table.Click [Finish].

That will list the Count of Event for each EventDate.

Not quite there yet...
Right-Click on the EventDate heading
Select: Group and Show Detail.......Group
Check: Year......Click [OK]

NOW the Pivot Table will list the Count of Event by Year.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)