using a year from a date
 

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.
--
andy

using a year from a date
 

For dates in A1 thru A20:

=SUMPRODUCT(--(YEAR(A1:A20)=2005))
--
Gary''s Student - gsnu200844


"andy" wrote:

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.
--
andy

using a year from a date
 

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 D3:D10, paste.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"andy" wrote in message
...
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.
--
andy

using a year from a date
 

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


"Gary''s Student" wrote:

For dates in A1 thru A20:

=SUMPRODUCT(--(YEAR(A1:A20)=2005))
--
Gary''s Student - gsnu200844


"andy" wrote:

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.
--
andy

using a year from a date
 

=SUMPRODUCT(--(YEAR(D3:D58)=1977))
Regards,
Stefi

€žandy€� ezt Ã*rta:

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.
--
andy