Formula question: timesheet spreadsheet template

[krosado]

Hello,

I am try to create a timesheet and I am having diffiulty with a formula.

At the bottome I would like to make a timesheet summary of all of the Paycode Lists.

EX: countifs? 4 [SALH] in both week 2 and week 3, 10 [MEAL] etc...

<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#333399 cellPadding=2 width=906><TBODY><TR><TD bgColor=#333399 height=20 width="11%">

WEEK 2​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="19%">
</TD><TD bgColor=#333399 height=20 width="9%">

Sunday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Monday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Tuesday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Wednesday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Thursday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Friday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Saturday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="8%">

Total​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD bgColor=#333399 height=19 width="11%">

Paycode​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=19 width="19%">

Job​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

22-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

23-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

24-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

25-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

26-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

27-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

28-May​

​

​

​

​

</TD><TD bgColor=#333399 height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">

4 [SALH]​

​

​

​

​

</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">

8.00​

​

​

​

​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">

8.00​

​

​

​

​

</TD></TR><TR><TD height=19 width="11%">

10 [MEAL]​

​

​

​

​

</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">

-​

​

​

​

​

</TD></TR><TR><TD height=20 width="11%">
</TD><TD height=20 width="19%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="8%">

-​

​

​

​

​

</TD></TR><TR><TD height=21 width="11%">

Daily Totals:​

​

​

​

​

​

</TD><TD height=21 width="19%">
</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

8.00​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="8%">

8.00​

​

​

​

​

</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD bgColor=#333399 height=20 width="11%">

WEEK 3​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="19%">
</TD><TD bgColor=#333399 height=20 width="9%">

Sunday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Monday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Tuesday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Wednesday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Thursday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Friday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="9%">

Saturday​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=20 width="8%">

Total​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD bgColor=#333399 height=19 width="11%">

Paycode​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#333399 height=19 width="19%">

Job​

​

​

​

​

​

​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

29-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

30-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

31-May​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

1-Jun​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

2-Jun​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

3-Jun​

​

​

​

​

</TD><TD bgColor=#ccccff height=19 width="9%">

4-Jun​

​

​

​

​

</TD><TD bgColor=#333399 height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">

4 [SALH]​

​

​

​

​

</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">

8.00​

​

​

​

​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">

8.00​

​

​

​

​

</TD></TR><TR><TD height=19 width="11%">

10 [MEAL]​

​

​

​

​

</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">

-​

​

​

​

​

</TD></TR><TR><TD height=20 width="11%">
</TD><TD height=20 width="19%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="9%">
</TD><TD height=20 width="8%">

-​

​

​

​

​

</TD></TR><TR><TD height=21 width="11%">

Daily Totals:​

​

​

​

​

​

</TD><TD height=21 width="19%">
</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

8.00​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="9%">

-​

​

​

​

​

</TD><TD height=21 width="8%">

8.00​

​

​

​

​

</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="30%" colSpan=2>

Timesheet Summary:​

​

​

​

​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">

Pay Code​

​

​

​

​

</TD><TD height=19 width="9%">

Reg Hrs​

​

​

​

​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">

4 [SALH]​

​

​

​

​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR></TBODY></TABLE>

Also, is there a way that if a cell is blank it will be marked as Zero or "-"?

 

[Marcelo Branco]

Hi,

Assuming PayCode in column A and Total in column J, maybe
=SUMIF(A1:A1000,"4 [SALH]",J1:J1000)

you can substitute "4 [SALH]" for a cell reference

HTH

M.

 

[krosado]

Does it make a difference if "4 [SALH]" is a option on a list?

<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#333399 cellPadding=2 width=906><TBODY><TR><TD height=19 width="30%" colSpan=2>

Timesheet Summary:​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">

Pay Code​

</TD><TD height=19 width="9%">

Reg Hrs​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">

4 [SALH]​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR></TBODY></TABLE><TABLE dir=ltr border=1 cellSpacing=1 borderColor=#333399 cellPadding=2 width=906><TBODY><TR><TD height=19 width="30%" colSpan=2>

Timesheet Summary:​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">

Pay Code​

</TD><TD height=19 width="9%">

Reg Hrs​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR><TR><TD height=19 width="11%">
</TD><TD height=19 width="19%">

4 [SALH]​

</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="9%">
</TD><TD height=19 width="8%">
</TD></TR></TBODY></TABLE>
<TABLE dir=ltr border=0 cellSpacing=0 cellPadding=2 width=336><TBODY><TR><TD height=19 width="76%" colSpan=2>

Timesheet Summary:​

​

​

​

​

</TD><TD height=19 width="24%">
</TD></TR><TR><TD height=19 width="24%">
</TD><TD height=19 width="52%">

Pay Code​

​

​

​

​

</TD><TD height=19 width="24%">

Reg Hrs​

​

​

​

​

</TD></TR><TR><TD height=19 width="24%">
</TD><TD height=19 width="52%">

4 [SALH]​

​

​

​

​

</TD><TD height=19 width="24%">

(? formula)​

​

​

​

​

</TD></TR><TR><TD height=19 width="24%">
</TD><TD height=19 width="52%">

3 [JURY]​

​

​

​

​

</TD><TD height=19 width="24%">

​

</TD></TR><TR><TD height=19 width="24%">
</TD><TD height=19 width="52%">

1 [HOL]​

​

​

​

​

</TD><TD height=19 width="24%">
</TD></TR></TBODY></TABLE>

 

[Marcelo Branco]

In which columns (cells) are you putting the Timesheet Summary below?

​

Timesheet Summary:

Pay Code Reg Hrs
4 [SALH] (? formula)
3 [JURY]
1 [HOL]

M.

 

[krosado]

ALL " 1 [HOL]- 13 [FLDY]" are all in Data Validation lists

I would like a summary at the bottom of the page so if you were to choose (ex: 4 [SALH] from the list then it would count the total in each of the weeks.

 

[Marcelo Branco]

ALL " 1 [HOL]- 13 [FLDY]" are all in Data Validation lists

I would like a summary at the bottom of the page so if you were to choose (ex: 4 [SALH] from the list then it would count the total in each of the weeks.

Ok, i understood that, but i need to know, to use the formula, in what cell(s), row(s), column(s) is the Summary.

Example, if you have

Row Column A ---Column B
100 PayCode --- Reg Hrs
101 4 [SALH]
102 [JURY]
....
....

We have to adjust the formula in B101, to avoid a circular reference, to something like
=SUMIF(A1:A98,A101, J1:J98)

A1:J98 ---> your data range

M.