Time Keeper...

ssjerauld · Apr 8th, 2002, 07:01 PM

Hi,
I have a payroll worksheet with 7 columns (among others) representing the days of the week. Time values are totaled,SUM, in the 8th column, no problems. I need to track any of three others variables within the daily cells, these are "V" (Vacation),"P" (Personal),"H"(Holiday). Each of the three text have their own column. I am trying to write a formula to total only the text cells using an 8 hr format i.e if C3=8V and F3=8V then K3=16. Likewise if C4=8V and H4=8H then K4=8 and M4=8 and so on. I hope I haven't totaly confussed everyone. I also would like to add shading to these formulas if possible but will be satisfied to attempt that at a latter date.

Help would be greatly appreciated, but I still just like to come here and read how you all help everyone else...

Thanks...Scott

Brian from Maui · Apr 8th, 2002, 07:40 PM

Aloha Scott,
I have a spreadsheet similar to yours. I have, as an example:
Names in column A
Work codes i.e. vac, sick etc. in column B
Hours in column C

=sumproduct($A$1:$A$50="name")*($B$1:$B$50="vac"),$C$1:$C$50)

What this does is search for name and code, and if criteria is met, sum cells in column C

Got this from Aladin!

ssjerauld · Apr 8th, 2002, 07:45 PM

Brian,
Thanks for the reply. Will try it as soon as I can and let you know how it works.

Brian from Maui · Apr 8th, 2002, 07:55 PM

Forgot to tell you, it's an array formula, enter Ctrl+Shift+Enter

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 07:01 PM	#1
ssjerauld New Member Join Date: Mar 2002 Location: Central NY(not the park) Posts: 14	Hi, I have a payroll worksheet with 7 columns (among others) representing the days of the week. Time values are totaled,SUM, in the 8th column, no problems. I need to track any of three others variables within the daily cells, these are "V" (Vacation),"P" (Personal),"H"(Holiday). Each of the three text have their own column. I am trying to write a formula to total only the text cells using an 8 hr format i.e if C3=8V and F3=8V then K3=16. Likewise if C4=8V and H4=8H then K4=8 and M4=8 and so on. I hope I haven't totaly confussed everyone. I also would like to add shading to these formulas if possible but will be satisfied to attempt that at a latter date. Help would be greatly appreciated, but I still just like to come here and read how you all help everyone else... Thanks...Scott

Apr 8th, 2002, 07:40 PM	#2
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Aloha Scott, I have a spreadsheet similar to yours. I have, as an example: Names in column A Work codes i.e. vac, sick etc. in column B Hours in column C =sumproduct($A$1:$A$50="name")*($B$1:$B$50="vac"),$C$1:$C$50) What this does is search for name and code, and if criteria is met, sum cells in column C Got this from Aladin!

Apr 8th, 2002, 07:45 PM	#3
ssjerauld New Member Join Date: Mar 2002 Location: Central NY(not the park) Posts: 14	Brian, Thanks for the reply. Will try it as soon as I can and let you know how it works.

Apr 8th, 2002, 07:55 PM	#4
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Forgot to tell you, it's an array formula, enter Ctrl+Shift+Enter