Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 623
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
attendance log gloria's day care New Users to Excel 1 February 5th 07 06:05 PM
Attendance spreadsheet Tammy Excel Worksheet Functions 1 February 16th 06 10:46 PM
time and attendance JGB Excel Worksheet Functions 0 January 25th 06 06:20 PM
Percentage of overall attendance tannersnonni Excel Discussion (Misc queries) 6 May 13th 05 03:26 PM
employee attendance ruth New Users to Excel 2 February 8th 05 05:01 PM


All times are GMT +1. The time now is 12:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"