Excel 2007 : counting certain YEAR from 1 sheet to another sheet

[jamila0823]

i have here lists of activities in sheet 1...

NAME ----------------------POSITION---------DATE---------------------ACTIVITY
brod pete----------------------MD----------------4/2/2004-----------------workshop
john lennon------------------NURSE-----------4/2/2004-----------------workshop
elvis presley----------------MEDTECH-------4/3/2005-----------------meeting
john meyer------------------NURSE------------4/3/2005---------------- meeting
manny pacquiao-----------MD-----------------4/6/2004-----------------monitoring
f. maywheater-------------NURSE------------4/6/2004-----------------monitoring
johnny bravo----------------MD------------------4/5/2005----------------evaluation

...and i have my formula in sheet 2. how can i get the total number of ACTIVITY that was done in year 2004 and the total number of NAME of participants per POSITION that was done also in year 2004. i should get the result:
# of activity in 2004 = 2 (workshop&monitoring)
# of MD in 2004 = 2
# of NURSE in 2004 = 2
total # of participants in 2004 = 4
please please help me...
i really had a headache in here..
any tips will be very much appreciated...

[Cutter]

To get you started:

With your data on Sheet1 in range A2:D8

For # of entries for 2004:
=SUMPRODUCT((YEAR(Sheet1!C2:C8)=2004)*1) gives 4

For # of nurses in 2004:
=SUMPRODUCT((Sheet1!B2:B8="nurse")*(YEAR(Sheet1!C2:C8)=2004)) gives 2

For # of workshops in 2004:
=SUMPRODUCT((Sheet1!D2:D8="workshop")*(YEAR(Sheet1!C2:C8)=2004)) gives 2

[jamila0823]

To get you started:

With your data on Sheet1 in range A2:D8

For # of entries for 2004:
=SUMPRODUCT((YEAR(Sheet1!C2:C8)=2004)*1) gives 4

For # of nurses in 2004:
=SUMPRODUCT((Sheet1!B2:B8="nurse")*(YEAR(Sheet1!C2:C8)=2004)) gives 2

For # of workshops in 2004:
=SUMPRODUCT((Sheet1!D2:D8="workshop")*(YEAR(Sheet1!C2:C8)=2004)) gives 2

wow! thank you so much! i appreciate!
but one more question please...(0_o)
how about if i want to get all the year 2004 in column C.. i mean, there's no specific range in column C but all the records that are entered in column C. coz' ive create a record which is a continuous database. so as the days go by, i should entered another record.
i try this but it does not work: =SUMPRODUCT((YEAR(Sheet1!C:C)=2004)*1)

sorry but i really need this... thanks again! u

[Cutter]

The easiest way to get the number of occurrences for 2004 would be:

=COUNTIF(Sheet1!C:C,">="&DATE(2004,1,1))-COUNTIF(Sheet1!C:C,">="&DATE(2005,1,1))


you could/should replace the 2004 with a cell reference holding that value. If you use, for example, D2 you would then have this as your formula:

=COUNTIF(Sheet1!C:C,">="&DATE(D2,1,1))-COUNTIF(Sheet1!C:C,">="&DATE(D2+1,1,1))