Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm working on a spreadsheet that allows some basic collaborative hour tracking.

  • Worksheet A contains an entity called Blocks where each block has a BlockID.
  • Worksheet B contains line items. Each Line item has a BlockID and # of hours spent for that block.

I would like to include the total hours spent per block (indicated in Worksheet B) in the Hours Spent column of Worksheet A.

Is this possible?

See sample screenshots:

Worksheet A

enter image description here

Worksheet B

enter image description here

I wonder if the SUMIF function might be useful

share|improve this question
    
Did you find one of the answers given useful? –  Jacob Jan Tuinstra Mar 14 at 7:19
add comment

migrated from superuser.com Jul 1 '11 at 16:50

This question came from our site for computer enthusiasts and power users.

2 Answers

You can do this with the DSUM function. The general form is:

=dsum([data range], [column to sum], [criteria])

[data range] would be the data in your Worksheet B, something like 'Worksheet B'!A:C where 'Worksheet B' is the actual name of the worksheet.

[column to sum] is the header of the column that you want to add up. In your case, that would be "Hours Spent".

[criteria] is the complicated part. You need to define the criteria for selecting which rows to sum. In your case, The total hours spent for "B1" is the sum of hours spent for each row where BlockID = "B1". You actually define this criteria in another place in your spreadsheet.

I recommend you create a new sheet named Criteria. On this sheet, in A1 put BlockID and in A2 put ="=B1". I know if looks funny, but you need to type it just like that.

(It occurs to me that you might run into trouble where your BlockIDs collide with cell references.)

Now the full formula for the sum of all hours spent on B1 is:

=dsum('Worksheet B'!A:C, "Hours Spent", Criteria!A1:A2)

For each new block ID, add another set of criteria to the Criteria sheet and modify the formula as necessary.


İmporting the another workbook you should learn its key like below:

enter image description here

=dsum(importRange("spreadsheet_key";'Worksheet B'!A:C), [column to sum], [criteria])
share|improve this answer
add comment

A solution to this problem is to utilize the SUMIF function.

=sumif('Hours Spent'!A3:A10001, A3, 'Hours Spent'!C3:C10001)

What's going on here?

  • We are referencing the other spreadsheet via the 'SheetName'! syntax
  • We are only including rows in the sum if the value in column A in the hours spent worksheet equals the value in A3 in the current worksheet. This enforces our BlockID mapping.
  • The final argument of the function indicates which column we are summing.

Reference

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.