Rosters and adding hours linked to a persons name

[MikeR-Oz]

Hi All,

I have a roster that goes across 8 weeks .
Each line (Say Line 2) corresponds to the number of hours in that week -
being line 2 that a person works . Line 2 is 44 hours, Line 5 may be 36 hours
etc etc.

At the end of the 8 weeks I want to know how many hours each staff member
has worked. Unfortunately staff do not start on a line and systematically
progress from week to week down the line numbers but will often chop and
change from line 2 to line 6 and then to line 4 etc. I enter the name of the
person on the line across the spreadsheet but how can I now have the program
look for the name and add the hours up associated with each 'liine' they work.

E.g.
Line 2 - 48 hours
Line 8 -36 hours
Line 4- xx
Line 5-xx
Line 6-xx
Line 2xx
Line 3 xx
Line 4- xx
TOTAL sum above XX-

 

[T. Valko]

Try this:

Names in column A
Hours in column B

=SUMIF(A1:A100,"Joe",B1:B100)

Or, use a cell to hold the name criteria:

D1 = Joe

=SUMIF(A1:A100,D1,B1:B100)

 

[MikeR-Oz]

HI, sorry for the delay in reply.

I am not sure what you mean.

I would have cell A1:100 as the lines numbering from A1 = to say 44 hours ,
A2 = 38 hours, A3 = 44 hours etc etc .

Then in B1:100 to XX1:100 being the names of people .

This would mean that john in B1 is = A1 which means John works 44 hours, he
appears again on C2 which is equal to 38 hours etc etc and then I wish to sum
all of these and like waise for all the other names from B1, C1 etc and down
the rows.

Is this clearer to work your sumif equation?

Mike

 

[T. Valko]

Try something like this:

=SUMPRODUCT((B1100="John")*A1:A100)

 

[MikeR-Oz]

Hi Biff , thanks for the continued assistance.

So the formula you suggest - so I understand it - Is if John apears anywhere
in the B1:100 to D1:100 range it will pull him out. But how does the formula
know if A11 is say 44 hours and A22 is 38 hours A33 is 44 hours etc to
connect John to the relevant A row and hours worked?

In adddition I would then like to have Bill, Karen etc all calculated for
their hours across each of the A1 1; A22; A33 etc fileds that each row
is different hours ?

If I undersatnd the approach you are suggesting I hope to be able to apply it.

Any comments for me?

Thanks
Mike

 

[MikeR-Oz]

Biff

This is what I have so far from your suggestiion and it works - looks a
little clumsy though? Can it be streamlined? And I would need to repeat for
each name that appears on the roster??

Mike

 

[T. Valko]

This is what I have so far from your suggestiion and it works - looks a

little clumsy though? Can it be streamlined?

I don't see anything!

Judging from your last reply I obviously don't understand how your data is
setup.

Explain in *very explicit detail* using the *REAL* range references how your
data is setup.

I'll check back tomorrow. It's 4:00 AM where I live and I'm calling it a
day!

 

[MikeR-Oz]

Wow Biff 4 am - I thought my sleep patterns were bad!!

Ok

A1- text "line 1"; A2 - Text "Line 2: etc down to A8 which says "line 8".

C1 John; D1: E1 has other peoples names,

I inserted column > B.

This Column going down rows 1 : 8 has the hours relating each line e.g.

B1 reflecting "Line 1"= 44 hours > I have the number 44 in B1.
B2 is 32 hours ;
B3= 38 hours
B4 =44 hours


F1 has fourmula you gave me =SUMPRODUCT((C1:E1="John")*B1).

I then repeat this for rows B2:B8 which is "Line 2" to "Line 8". A line is
a week and this spreadsheet has 8 actual weeks.


At F9 I sum all F1:F8 and this gives me the total hours for any 'John' that
has apperaed in C1:E1; C2:E2; C3:E3 etc etc down to C8:E8

D1 cell has name Kate

E1 has Bob


So I would then use your formulae =SUMPRODUCT((C1:E1="Kate")*B1) and put the
total in G1 and then repeat all the way down the rows to row 8. Same for Bob
at H1:H8.

This approach would pick up their names if they appeared in any "Line" and
calculate their hours.

As I mentioned it seems quite labour intensive and was hoping for something
?quicker and more formulae driven?

It does work this way though but if I get it easier it would be better yet.

I have thought of putting all the names - 30 in real life in their own cells
refernce such as X1 is John, X2 Kate, X3 Bob etc and then use that specific
cell reference in your product formulae instaed of writing their name - would
that work?

The object is to calculate any persons hours that are shown next to a
specific "line number" as each line has a set number of hours assigned to it.
Usually a person would go diagonally down the roster e.g C1; D2; E3; F3 etc
to 8 But they often have to swap around C1; D2; E3; F1 (should be F4) etc.

Does this help?

Mike

 

[T. Valko]

Does this help?

Yes!

Ok, list the names in a range of cells, say, G1:G30

Enter this formula in H1 and copy down to H30:

=SUMPRODUCT((C$1:E$8=G1)*B$1:B$8)

Here' a small sample file that demonstrates this:

xMikeR.xls 14kb

http://cjoint.com/?dyvUgc4ht4

 

[MikeR-Oz]

You are a good man Biff - terrific - I will play around with it now , but it
looks very good.

Appreciate all your assistance.

Mike

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[MikeR-Oz]

Biff, I wuld like to do a little more with what you have already shown me.
Would you like to assist?
Can I email back the small file you sent me and I will add the extra things
I would like to do?

I want to use worksheets within the work book and have another roster group
of names and then have all of the names from both rosters use your program
but show the result on a seperate sheet with all the names in a top to bottom
lists.

And lastly can excel formulas pick up the fact that a name (Bob) appears on
Line 1 (C1) and reflect that to mean 40 hours on the seperate sheet against
Bob in B1 for example.

Cheers

Mike