counting data across worksheets

[franki_85]

I need to do the following:-

I have 12 worksheets (one for each month) which contain information on staff
members, when they were on duty, when they were on holiday etc.

I have successfully used COUNTIF to work out how many days holiday each
staff member has had at the end of each month (defined by "L"). using

=COUNTIF(D4:AH4, "L")

then dragging the formula down for each staff member.

The following is not the exact information, and is only an example.

Column A is staff number, B is the persons name, C-I are the dates of the
month and Column J is a COUNTIF formula to count the amount of L's across the
row.

The rows contain each employees work and shift pattern details for that month.

The COUNTIF formula has been successful, however some staff members have now
left their job, and their roles have been filled by someone else. Therefore
the
information across the worksheets is not consistent.

eg in Jan Mr A is in Column B Row 1
in Feb he is in Column B Row 4
in Mar he is in Column B row 3

The COUNTIF formula calculates the correct information in column J at the
ends of rows 1, 4 and 3 for Mr A, but it is only on a month by month basis.

I want to have a summary for each staff member on a 13th worksheet, and
have a running total with how many days leave he has left by counting the
times he has been off and subtracting that from his entitlement (which
is 44 days).

I assume i need to count the values in column J, but i dont know how to
correspond that with column B

eg in simple terms,

January B1 + February B4 + March B3 etc

Is there any way i can do this using a formula or any other excel feature?

I'm sorry if this is very confusing, and any help is greatly appreciated!

 

[Stephen]

I need to do the following:-

I have 12 worksheets (one for each month) which contain information on
staff
members, when they were on duty, when they were on holiday etc.

I have successfully used COUNTIF to work out how many days holiday each
staff member has had at the end of each month (defined by "L"). using

=COUNTIF(D4:AH4, "L")

then dragging the formula down for each staff member.

The following is not the exact information, and is only an example.

Column A is staff number, B is the persons name, C-I are the dates of the
month and Column J is a COUNTIF formula to count the amount of L's across
the
row.

The rows contain each employees work and shift pattern details for that
month.

The COUNTIF formula has been successful, however some staff members have
now
left their job, and their roles have been filled by someone else.
Therefore
the
information across the worksheets is not consistent.

eg in Jan Mr A is in Column B Row 1
in Feb he is in Column B Row 4
in Mar he is in Column B row 3

The COUNTIF formula calculates the correct information in column J at the
ends of rows 1, 4 and 3 for Mr A, but it is only on a month by month
basis.

I want to have a summary for each staff member on a 13th worksheet, and
have a running total with how many days leave he has left by counting the
times he has been off and subtracting that from his entitlement (which
is 44 days).

I assume i need to count the values in column J, but i dont know how to
correspond that with column B

eg in simple terms,

January B1 + February B4 + March B3 etc

Is there any way i can do this using a formula or any other excel feature?

I'm sorry if this is very confusing, and any help is greatly appreciated!

On Sheet13 I would pull the data for each month for each employee, and then
simply add the twelve columns to get the total.
Assuming
1 Up to 1000 rows of data (you can adjust as necessary)
2 Employee name "Mr A" in Sheet13!A1

This formula in Sheet13!B1 would get the data for January:
=SUMPRODUCT((Sheet1!B1:B1000=A1)*(Sheet1!J1:J1000))

A similar formula in Sheet13!C1 would get data for February:
=SUMPRODUCT((Sheet2!B1:B1000=A1)*(Sheet2!J1:J1000))
and so on for 12 months.

In Sheet13!N1 a simple formula gives the year total:
=SUM(B1:M1)
These 13 formulas can then be copied down as far as necessary for all
employees listed in column A.

 

[franki_85]

thanks! ill try that.

On Sheet13 I would pull the data for each month for each employee, and then
simply add the twelve columns to get the total.
Assuming
1 Up to 1000 rows of data (you can adjust as necessary)
2 Employee name "Mr A" in Sheet13!A1

This formula in Sheet13!B1 would get the data for January:
=SUMPRODUCT((Sheet1!B1:B1000=A1)*(Sheet1!J1:J1000))

A similar formula in Sheet13!C1 would get data for February:
=SUMPRODUCT((Sheet2!B1:B1000=A1)*(Sheet2!J1:J1000))
and so on for 12 months.

In Sheet13!N1 a simple formula gives the year total:
=SUM(B1:M1)
These 13 formulas can then be copied down as far as necessary for all
employees listed in column A.

 

[Don Guillett]

See answers in the .misc group and pls do NOT post in more than ONE group. A
waste of time.

 

[franki_85]

Apologies for double posting.

I am a new member, and didnt realise there were different discussion groups
until after my first post. I only re-posted as i assumed people with
different expertise would view the discussion group with which they were most
familiar. In this case Worksheet functions.

Apologies once again.