I use a timesheet in my job & I want to total project times over the course
of the year.

There are 52 sheets, one for each work week, plus a totals sheet. I want
excel to look in column c for of each sheet a given project number and add
the time in column f. I can do it for one sheet but I get value! when I try
to specify a range of sheets.

The sheets are set up like this:

Date Acronym Project No. Phase Description Hours
11-20-09 CHLFD 091102 V Plans 7.5
11-20-09 JDSF 091021 C ELEVATION 2.0
11-21-09 CHLFD 091102 V Plans 9.5


I can get it to do one sheet with
=sumif('week 21'!C9:C31,091102,'week 21'!F9:F31)

When I try to do a range of sheets with
=sumif('week 21:week 45'!C9:C31,091102,'week 21:week 45''!F9:F31)
I get a value! error.

Thanks.

Try the below

=SUMPRODUCT(SUMIF(INDIRECT("'"&"week "&
ROW(INDIRECT("21:45"))&"'!C9:C31"),091102,INDIRECT ("'"&"week "&
ROW(INDIRECT("21:45"))&"'!F9:F31")))

If this post helps click Yes
---------------
Jacob Skaria


"cwcordell" wrote:

I use a timesheet in my job & I want to total project times over the course
of the year.

There are 52 sheets, one for each work week, plus a totals sheet. I want
excel to look in column c for of each sheet a given project number and add
the time in column f. I can do it for one sheet but I get value! when I try
to specify a range of sheets.

The sheets are set up like this:

Date Acronym Project No. Phase Description Hours
11-20-09 CHLFD 091102 V Plans 7.5
11-20-09 JDSF 091021 C ELEVATION 2.0
11-21-09 CHLFD 091102 V Plans 9.5


I can get it to do one sheet with
=sumif('week 21'!C9:C31,091102,'week 21'!F9:F31)

When I try to do a range of sheets with
=sumif('week 21:week 45'!C9:C31,091102,'week 21:week 45''!F9:F31)
I get a value! error.

Thanks.

Thanks for your reply Jacob.

I put the function in just as you have it shown & got a #ref!. Do I need to
edit it in any way, such as insert sheet names? I'm a novice so I don't
understand what most of that function contains. I'm familiar with sumif but
the other and all the " I have not a clue. Thank you for your time and
patience.

"Jacob Skaria" wrote:

Try the below

=SUMPRODUCT(SUMIF(INDIRECT("'"&"week "&
ROW(INDIRECT("21:45"))&"'!C9:C31"),091102,INDIRECT ("'"&"week "&
ROW(INDIRECT("21:45"))&"'!F9:F31")))

If this post helps click Yes
---------------
Jacob Skaria


"cwcordell" wrote:

I use a timesheet in my job & I want to total project times over the course
of the year.

There are 52 sheets, one for each work week, plus a totals sheet. I want
excel to look in column c for of each sheet a given project number and add
the time in column f. I can do it for one sheet but I get value! when I try
to specify a range of sheets.

The sheets are set up like this:

Date Acronym Project No. Phase Description Hours
11-20-09 CHLFD 091102 V Plans 7.5
11-20-09 JDSF 091021 C ELEVATION 2.0
11-21-09 CHLFD 091102 V Plans 9.5


I can get it to do one sheet with
=sumif('week 21'!C9:C31,091102,'week 21'!F9:F31)

When I try to do a range of sheets with
=sumif('week 21:week 45'!C9:C31,091102,'week 21:week 45''!F9:F31)
I get a value! error.

Thanks.