I'm as lost as Topper. How do you get 3 annuals for Peter from the sample data?
And how does John get 4, and George 0?

If you can explain how to get the results you've shown, I'm sure someone can
come up with a formula.

--
Regards,
Fred


"Pietro" wrote in message
...
Here's a sample:
ATTENDANCE TAB:
A b c d e
1 Peter attended attended attended attended
2 John AL attended attended attended
3 George Absent AL attended attended

Annual leave TAB:
A b c d e
Name Annuals taken
1 Peter 3 ...........I need tocalculate the number of annuals
taken her
2 John 4
3 George


"Toppers" wrote:

When you say in front of do mean AL in column A or "AL Peter"?

Sorry to be so dim!

"Pietro" wrote:

Thank you for your answer ,but actually i need to count the word "AL" for
each employee.
Example i want to count "AL" found infront of the employee "peter"

"Toppers" wrote:

If I understand correctly, you want count of number of times "AL" appears
in
Columns D to R.

Try this in column F (assuming data starts in Row 2):

=COUNTIF(OFFSET(Attendance!B2,MATCH('Annual
Leaves'!B2,Attendance!B2:B100,0)-1,2,1,15),"=AL")

Change B range to suit

HTH

"Pietro" wrote:

Hi all,
I need somebody to help me to do the following:

I have an excel sheet that has two tabs:
1-In the first one"attendance" ,in column B,I have the names of my
employees,and then from column D to column R the weekly attendance.
2-In the second tab"Annual leaves" i have these names in column B,then
in
column F i want to count how many times was the word "AL" written
infront of
each name in tab "attendance" from column D to column R,i need the
results of
this count to be displayed infront of each name in the second sheet
"Annual
leaves"