vookup and counting

[DEVIL4400]

i have a excel sheet like this as below


no name started date end date no days remarks

2345 john 23/04/2010 27/04/2010 5 annual leave
2344 muray 09/02/2010 10/10/2010 30 annual leave
2345 john 1/1 /2011 7/1/2011 8 annual leave



i wanted to the john total days which have taken for annual leave and view its using vlook up in another sheet and seperatly view total number of days he has taken

 

[CharlesChuckieCharles]

i have a excel sheet like this as below


no name started date end date no days remarks

2345 john 23/04/2010 27/04/2010 5 annual leave
2344 muray 09/02/2010 10/10/2010 30 annual leave
2345 john 1/1 /2011 7/1/2011 8 annual leave



i wanted to the john total days which have taken for annual leave and view its using vlook up in another sheet and seperatly view total number of days he has taken

Try SUMIF

=SUMIF(B1:B100,"John",E1:E100)

will return 13

SUMIF is ( "Key" Range, "KEY", "Value to Sum" Range )


replace John with a Cell reference say B1