# How do I populate a cell in Worksheet A with aggregate data from Worksheet B?

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

Worksheet B

I wonder if the SUMIF function might be useful

-

## migrated from superuser.comJul 1 '11 at 16:50

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

Did you find one of the answers given useful? – Jacob Jan Tuinstra Mar 14 '14 at 7:19

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 `BlockID`s 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:

``````=dsum(importRange("spreadsheet_key";'Worksheet B'!A:C), [column to sum], [criteria])
``````
-

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

-