total random cells

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I'm working on an "hours on call" sheet for our surgery
dept. I've got an IF statement set up for their names --
=IF(B5="bl","Brenda",IF(B5="jt","Jan",IF
(B5="sg","Shelli",IF(B5="kt","Kim",IF(B5="kr","Kraig",IF
(B5="sr","Sheila","-----")))))) -- and then the next
column is where they input the actual hrs the individual
was 'on call'. How can I total each person's hours in a
separate cell at the bottom of the sheet? New to excel
functions and would appreciate any suggestions. Thanks!
Linda
 
Linda said:
I'm working on an "hours on call" sheet for our surgery
dept. I've got an IF statement set up for their names --
=IF(B5="bl","Brenda",IF(B5="jt","Jan",IF
(B5="sg","Shelli",IF(B5="kt","Kim",IF(B5="kr","Kraig",IF
(B5="sr","Sheila","-----")))))) ...
Click to expand...

Frank has answered your Q.

Just a lateral suggestion for a better alternative instead of using IF?
[ Looks like you're going to hit the nested IF limit of 7 pretty soon ]

Set-up a table in a new Sheet2 [say]
in cols A & B, data from row2 down

Name Initial
Brenda..bl
Jan........jt
Shelli.....sg
Kim.......kt
Kraig.....kr
Sheila.....sr
etc

-----
Supposing the initials are in Sheet1, col B, row2 down

you can put in say, C2:

=IF(ISNA(OFFSET(Sheet2!$A$1,MATCH(TRIM(B2),Sheet2!B:B,0)-1,0)),"------",OFFS
ET(Sheet2!$A$1,MATCH(TRIM(B2),Sheet2!B:B,0)-1,0))

and just copy C2 down as many rows as there is data in col B

Col C will return the Names matching the initials in col B

Unmatched cases will be returned with "------"

TRIM() is used to increase the robustness of the match,
in case there are any inadvertent leading or trailing spaces
in the initials entered in col B

The above set-up is, IMO, much easier to maintain
compared to a nested IF
and there's also no limits to worry about
[just add any new names and initials to the table in Sheet2 when required
- no need to amend the formula]
 
Just a slight add-on to Frank's suggestion ..

If the Names retrieved are in col C and the hours are in col D
[ as per Frank's assumptions ], and are in *Sheet1*

In a new Sheet3, put the staff names in col A, row2 down, viz.:

Brenda
Jan
Shelli
Kim
Kraig
Sheila

Put in B2 (in Sheet3): =SUMIF(Sheet1!C:C,A2,Sheet1!D:D)
Copy B2 down as many rows as there are names in col A

Col B will give you the total hours corresponding to each name in col A
--------

Btw, think there's a small typo in Frank's SUMIF - the last part
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$1009
Click to expand...

should read
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$100)
Click to expand...

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Frank Kabel said:
Hi
several ways:
1. With formulas. Lets assume column C stores your names and column D
the hours use
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$1009
and format this cell with the custom format [hh]:mm

2. You also may use a pivot table which creates a report for all of you
employees. Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
to get startet with them


--
Regards
Frank Kabel
Frankfurt, Germany
I'm working on an "hours on call" sheet for our surgery
dept. I've got an IF statement set up for their names --
=IF(B5="bl","Brenda",IF(B5="jt","Jan",IF
(B5="sg","Shelli",IF(B5="kt","Kim",IF(B5="kr","Kraig",IF
(B5="sr","Sheila","-----")))))) -- and then the next
column is where they input the actual hrs the individual
was 'on call'. How can I total each person's hours in a
separate cell at the bottom of the sheet? New to excel
functions and would appreciate any suggestions. Thanks!
Linda
Click to expand...
Click to expand...
 
Hi Max
thanks for the addition and correction :-)

--
Regards
Frank Kabel
Frankfurt, Germany
Just a slight add-on to Frank's suggestion ..

If the Names retrieved are in col C and the hours are in col D
[ as per Frank's assumptions ], and are in *Sheet1*

In a new Sheet3, put the staff names in col A, row2 down, viz.:

Brenda
Jan
Shelli
Kim
Kraig
Sheila

Put in B2 (in Sheet3): =SUMIF(Sheet1!C:C,A2,Sheet1!D:D)
Copy B2 down as many rows as there are names in col A

Col B will give you the total hours corresponding to each name in col
A --------

Btw, think there's a small typo in Frank's SUMIF - the last part
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$1009
Click to expand...

should read
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$100)
Click to expand...

--
Rgds
Max
xl 97
Hi
several ways:
1. With formulas. Lets assume column C stores your names and column D
the hours use
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$1009
and format this cell with the custom format [hh]:mm

2. You also may use a pivot table which creates a report for all of
you employees. Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
to get startet with them


--
Regards
Frank Kabel
Frankfurt, Germany
I'm working on an "hours on call" sheet for our surgery
dept. I've got an IF statement set up for their names --
=IF(B5="bl","Brenda",IF(B5="jt","Jan",IF
(B5="sg","Shelli",IF(B5="kt","Kim",IF(B5="kr","Kraig",IF
(B5="sr","Sheila","-----")))))) -- and then the next
column is where they input the actual hrs the individual
was 'on call'. How can I total each person's hours in a
separate cell at the bottom of the sheet? New to excel
functions and would appreciate any suggestions. Thanks!
Linda
Click to expand...
Click to expand...
Click to expand...
 
Hi Frank and Max --
Thanks so much for your help. I got it to work! You guys
are the best. I am concerned about the nested IF statement
limitations and am working on the other formula that Max
suggested. I haven't gotten that one figured out yet but
I'll keep working on it. Many thanks again for all your
input!
Linda
-----Original Message-----
Hi Max
thanks for the addition and correction :-)

--
Regards
Frank Kabel
Frankfurt, Germany
Just a slight add-on to Frank's suggestion ..

If the Names retrieved are in col C and the hours are in col D
[ as per Frank's assumptions ], and are in *Sheet1*

In a new Sheet3, put the staff names in col A, row2 down, viz.:

Brenda
Jan
Shelli
Kim
Kraig
Sheila

Put in B2 (in Sheet3): =SUMIF(Sheet1!C:C,A2,Sheet1!D:D)
Copy B2 down as many rows as there are names in col A

Col B will give you the total hours corresponding to each name in col
A --------

Btw, think there's a small typo in Frank's SUMIF - the last part
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$1009
Click to expand...

should read
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$100)
Click to expand...

--
Rgds
Max
xl 97
Hi
several ways:
1. With formulas. Lets assume column C stores your
Click to expand...
Click to expand...
names and column
D
the hours use
=SUMIF($C$1:$C$100,"Brenda",$D$1:$D$1009
and format this cell with the custom format [hh]:mm

2. You also may use a pivot table which creates a report for all of
you employees. Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
to get startet with them


--
Regards
Frank Kabel
Frankfurt, Germany

Linda wrote:
I'm working on an "hours on call" sheet for our surgery
dept. I've got an IF statement set up for their names --
=IF(B5="bl","Brenda",IF(B5="jt","Jan",IF
(B5="sg","Shelli",IF(B5="kt","Kim",IF (B5="kr","Kraig",IF
(B5="sr","Sheila","-----")))))) -- and then the next
column is where they input the actual hrs the individual
was 'on call'. How can I total each person's hours in a
separate cell at the bottom of the sheet? New to excel
functions and would appreciate any suggestions. Thanks!
Linda
Click to expand...
Click to expand...

.
Click to expand...
 
You're welcome, Frank!

A small typo or 2 here and there for one going at
a marvellously *stupendous* answering rate of 80+ posts a day
[ref.: Debra D's latest xl ng stats for Feb'04] is extremely
*insignificant*.

Btw, how do you get by with just 1 hour of sleep daily <vbg> ??
 
Hi Max
why sleep :-)

--
Regards
Frank Kabel
Frankfurt, Germany
You're welcome, Frank!

A small typo or 2 here and there for one going at
a marvellously *stupendous* answering rate of 80+ posts a day
[ref.: Debra D's latest xl ng stats for Feb'04] is extremely
*insignificant*.

Btw said:
Hi Max
thanks for the addition and correction :-)
Click to expand...
Click to expand...
 
Back
Top