Time sheet calculations

[ab]

Excel 2003 - hope this is the right group to ask.

I am trying to find a formula for a time sheet for employees. I will try to
show an example:

Col A (job number) Col B hours in day
2103x 4.0
2402 2.0
1234 2.0
___
sub-total 8.0

This continues in four blocks for each working day of the month. At the end
of block 4, I need a total of all the sub-totals. The person who set up the
sheet just had:
=SUM(C14+C23+C32+C41+C50+C59+C67+C75+G14+G23+ G32+G41+G50+ G59+G67+G75+
K14+K23+K32+K41+K50+K59+K67+K75+ O14+O23+O32+O41+O50+O59+O67 etc, but I'm
sure there must be an easier way!!

I would really appreciate some advice - I have tried using Help but don't
really know how to phrase the question!

TIA

 

[Guest]

You might want to reformat the sheet using the subtotal() function
check help for details
The subtotals would thewn truely be sutotals and the master total would not
count the subtotals shown
in other words if C14 is =subtotal(9,C6:C13)
and C23 =subtotal(9,C15:C22)
etc
and in c68 you wanted a true total
=subtotal(9,C6:C67) would be the total ignoring all of the subtotal in the
range.

in other words your final equation might be something like
=subtotal(9,C6:C67,G6:G67,K6:K67,O6:O67)
as long as all of the current subtotals were made using subtotal()

 

[Domenic]

Try...

=SUMPRODUCT(--(MOD(ROW(C14:C75)-ROW(C14),9)=0),(C14:C75+G14:G75+K14:K75+O
14:O75))

If you have more columns, let's say Column C to Column Z, try the
following instead...

=SUM(IF(MOD(ROW(C14:C75)-ROW(C14),9)=0,IF(MOD(COLUMN(C14:Z14)-COLUMN(C14)
,4)=0,C14:Z75)))

This formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

 

[ab]

Thanks so much - that's just what I need.

I'm probably pushing it a bit but on the same worksheet - further down so on
Page 2 - there is a two-column summary sheet so that we can see how many
hours have been spent on a particular job during the month. At present, I
have to go through Page 1 and pick out all hours spent on, say, Job 2000 and
enter a total in the summary followed by all other jobs worked on:

2000 44
1234 86 etc.
The sum at the end of this table *should* equal the Total on the Final
Equation on the first sheet!

Is it possible to create a formula for this - with so many jobs over the
month, I sometimes miss something the first time and have to go over it all
again!