Count from ATTENDANCE to ANNUAL

[Pietro]

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"

 

[Toppers]

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]

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]

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

Sorry to be so dim!

 

[Pietro]

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

 

[Fred Smith]

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.

 

[Toppers]

You ARE confusing us! In your original posting, you said names were in column
B and attendance data in columns D to R yet your example shows names in A
and attendance data in B onward!

My original posting does count the number of "AL" entries for each name
(drag the formula down in "Annual Leave" tab).

The formula below has been modified to look at names in column A with data
starting in B. The 15 refers to the number of data columns (originally D to
R).

=COUNTIF(OFFSET(Attendance!A2,MATCH('Annual
Leaves'!A2,Attendance!A2:A100,0)-1,1,1,15),"=AL")


......but, like Fred, I don't understand how you get your results!