I am looking to automate our timesheet process. Each staff member has their own separate Google Sheet which has the dates down one column, and then a varying number of columns named after different projects they are working on.

I want to create an automatic summary of the total number of hours by project by staff member in another Google sheet. Like this:

enter image description here

I know I can use importRange to pull in numbers from specific cells across a number of different workbooks. But I am struggling to find a way to sum the hours by project without making every staff member have all 60+ projects on their timesheet and keep these in the same order so the cell references never change and break the formulas.

I want a formula which sums numbers in a column IF the header of that column is a specific name e.g. = Project 1.

So the range of the formula would cover a whole spreadsheet, but would only sum 1 column based on the header.

Any ideas?

  • Not sure I'm understanding the problem here but is someting like =IF(B3="Project 1",SUM(B4:B17),) ok or not what you expect ?
    – pjmg
    Commented Aug 14, 2019 at 20:23

1 Answer 1


Just have the hours summed up in each member's sheet and import the sums which you can again sum up for the grand totals.

Please have a look at this sample sheet I prepared for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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