Multi-sheet sumif help

[Brian]

Howdy All,

I have a workbook with 6 worksheets in it that each contain the data for 6
departments.
The data format on each worksheet is identical.
I have a column on each worksheet for the date (Column B) and another for
the production volume on that date (Column T). The is other date on the
worksheets, but I believe we only need be concerned with these two columns.

On another worksheet called TOTALS, I want to display a total for each date
across the 6 worksheets. For example on 12/15/2006, work was done in 3 of
the 6 departments. I want to display that date and the 3 department total on
the TOTALS worksheet.
Is there a way to list the dates without duplicates and display the totals
by date across all the departments?

I hope I am clear.
Thanks for your input!

Brian

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2,3,4,5,6}&"'!C1:C1000"),K1,INDIRECT("'Sheet"&{1,2,3,4,5,6}&"'!T1:T1000")))

which assumes that your sheets are named Sheet1,2,3,etc. and the date is in
K1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Brian]

Thanks Bob.

But it gives me an error on the first Sheet name. I replaced the '1' with
the first sheet name (which is actually MHO) and it errors on it. Any Ideas?

 

[Lori]

For a list of dates, fill down from the first row:

=SMALL(IF(FREQUENCY(Sheet1:Sheet6!
B:B,ROW($36526:$40179)),ROW($36526:$40179)),ROW())

assuming all dates are between Jan-00 and Jan-10. Then use sumif
formulas for the six cols:

=SUMIF(Sheet1!B:B,A1,Sheet1!T:T)

etc. Alternatively, if you know pivottables, use the multiple
consolidation sources option with e.g. sheet1!B1:T1000,...,sheet6!
B1:T1000 as ranges

 

[Bob Phillips]

If the sheet names are not an ordered list, like Sheet1, Sheet2, then put
the sheet names in M1:M6 and use

=SUMPRODUCT(SUMIF(INDIRECT("'"&M1:M6&"'!C1:C1000"),K1,INDIRECT("'"&M1:M6&"'!T1:T1000")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)