Timesheet Formula Hair Pull!

[gloriousglenn]

I have posted this query elsewhere (here) but I may not have worded it as well as I could and I didn't get very far....So here's hoping I can get the wording right this time and get my problem solved!!

I have multiple sheets in one spreadsheet.

I have employee numbers and then hours they have worked each day.

I would like to have a separate sheet with a cell that says "If a specific cell has employee number 1234 then add up the hours in that row" but the total needs to be a combined total of any occurrences of that employee number in each sheet.

I'm not even sure if that makes any better sense than my post on the other thread!!!

I have uploaded a test file to fileden (click here) in a hope that viewing an example will make it a bit clearer!

Here's hoping and thank you very much in advance.

Kind regards
Glenn

 

[baodinh]

A sumif formula should resolve your problem. In you example spreadsheet, put this formula in cell C3 in "Overall Hours".

=SUMIF('Site 1'!$A$4:$A$15,'Overall Hours'!A3,'Site 1'!$M$4:$M$15)+SUMIF('Site 2'!$A$4:$A$15,'Overall Hours'!A3,'Site 2'!$M$4:$M$15)+SUMIF('Site 3'!$A$4:$A$15,'Overall Hours'!A3,'Site 3'!$M$4:$M$15)

 

[gloriousglenn]

That, my friend, is exactly what I am after. Thank you so much! You wouldn't believe the time I've spent trying to sort that out! Many, many, many thanks.

 

[gloriousglenn]

I have seen "$" used a lot in formulas before but never understood what it does! In the above example. What is benefit of the "$"? Could I not just use:

=SUMIF('Site 1'!A:A,'Overall Hours'!A3,'Site 1'!M:M)+SUMIF('Site 2'!A:A,'Overall Hours'!A3,'Site 2'!M:M)+SUMIF('Site 3'!A:A,'Overall Hours'!A3,'Site 3'!M:M)

I'm not knocking your formula, just trying to understand.

Also...Is there a way that instead of having to put 'Site 1', 'Site 2', Site 3' etc into the formula that it just looked in every sheet? The reason I ask this is because I have new 'Sites' added each week and that would mean I would have to change the formula constantly.

Fingers crossed!
Glenn

 

[Joe4]

I have seen "$" used a lot in formulas before but never understood what it does! In the above example. What is benefit of the "$"?

See here: http://www.cpearson.com/excel/relative.aspx