Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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" |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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" |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count from ATTENDANCE to ANNUAL
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! "Fred Smith" wrote: 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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
attendance log | New Users to Excel | |||
Attendance spreadsheet | Excel Worksheet Functions | |||
time and attendance | Excel Worksheet Functions | |||
Percentage of overall attendance | Excel Discussion (Misc queries) | |||
employee attendance | New Users to Excel |