Adding up cell values only with identical info in other cells

[rarch1]

What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up only
those cells that have all the same number (i.e. job no.)in a different column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to the
source, so if I change or clear the hours cell or the job no. cell in the
source range, the totals reflect it.
Is there a way to do this? Please help.

 

[Gary''s Student]

A Pivot Table will do exactly what you want:

Sum of Hours by Job number.

see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Sandy Mann]

Try:

=SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$B$2:$B$7)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

But a pivot table will not update the way the the OP want it to

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[rarch1]

SUMIF works if I only want to reference a single source, but what if I need
to be linked to multiple worksheets or workbooks?

 

[Herbert Seidenberg]

Here are several methods that work across
multiple worksheets. Includes Pivot Table.
Excel 2003
http://www.freefilehosting.net/download/3bm61

 

[rarch1]

Thanks for the help. It looks like I can do this really well with a pivot
table, but I'll have to work with it a bit and set up the source ranges a
little differently. But I also got the SUMIF formula to work also using
SUMIFS to add multiple criteria similar to fields in the pivot table and then
stringing the function together for multiple sheet references. Example
=SUMIFS(Sheet1!B2:B7,Sheet1!A2:A7,"408")+SUMIFS(Sheet2!B2:B7,Sheet2!A2:A7,"408")+...and so on... for the hours for '408'
=SUMIFS(Sheet1!B2:B7,Sheet1!A2:A7,"601")+SUMIFS(Sheet2!B2:B7,Sheet2!A2:A7,"601")+...and so on... for the hours for '601'.
This SUMIFS function method works for what I need right now, but it takes
lots of time inserting repetitive formulas for each field. I think the pivot
table will be more versatile - but I'll need to get more acquainted with all
the options first.
Thanks to everyone for your help on this!

rarch1

 

[Roger Govier]

Hi Sandy

Whilst I agree that PT's do not update automatically, it is only a single
line of code set in a worksheet activate event to make it do so

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

The name of the PT would need to be set to the name of the PT on the OP's
worksheet. This can be found by right clicking on the PT>Table Options>Name

 

[Sandy Mann]

Not only that but it looks like the OP has decided to go down the PT road as
well.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[ryguy7272]

Dang! Just take a look at Herbert's multiple methods and you can begin to
appreciate how powerful Pivot Tables really are!