Multi-sheet sumif help
 

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

Multi-sheet sumif help
 

=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" wrote in message
...
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

Multi-sheet sumif help
 

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?


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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

Multi-sheet sumif help
 

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



On 20 Jul, 16:04, "Brian" wrote:
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?

"Bob Phillips" wrote in message

...



=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" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Multi-sheet sumif help
 

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)

"Brian" wrote in message
...
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?


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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