using a year from a date

[andy]

I am working on a seniority list and want excel to list the number of people
hired in each specific year. What is the formula I would use to just extract
that year of hire information? Sheet 1 lists all the seniority dates and
sheet 2 shows all the statistics so i have to refer to sheet 1 in the
formula. I was trying to use =countif(senioritylist!d3:d58,"="(year)1977)
and so on for consecutive years, but that is not working.

 

[Gary''s Student]

For dates in A1 thru A20:

=SUMPRODUCT(--(YEAR(A1:A20)=2005))

 

[Jon Peltier]

I used this setup in a quick test:

Dates in A2:A27
Years to test in C2:C10

Formula in D2:
=SUM(--(YEAR($A$2:$A$27)=C2))
Array-entered: hold Ctrl+Shift while pressing Enter
If done correctly, Excel puts {curly braces} around the formula

Copy D2, select D310, paste.

- Jon

 

[andy]

thanks again, you helped me again! that was the last formula i needed for
this list.

 

[Stefi]

=SUMPRODUCT(--(YEAR(D358)=1977))
Regards,
Stefi

„andy†ezt írta: