I would like to lookup and total values in multiple worksheets, The
worksheets have colums of labels and values and I want to total the values of
each label in one worksheet.

Robert Lawrence wrote...
I would like to lookup and total values in multiple worksheets, The
worksheets have colums of labels and values and I want to total the
values of
each label in one worksheet.

Skimpy on the details, but if you want to sum conditionally over
several workbooks, you'll need to use some trickery. First, create a
list of the worksheets to process in a range somewhere and name that
range something like WSLST. Then use a formula like

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSLST&"'!A2:A1001") ,"="&X99,
INDIRECT("'"&WSLST&"'!C2:C1001")))

to sum values in C2:C1001 in the worksheets included in WSLST
corresponding to values in A2:A1001 equal to the value in cell X99 in
the worksheet containing this formula.

The trick here is that when you pass INDIRECT an array first argument,
it returns something that appears to function as an array of range
references. Most Excel functions can't handle that, but SUMIF and
COUNTIF can, and they return array results when passed apparent arrays
of range references.

Sorry about the lack of details....

Let me try again: 5 worksheets each worksheet is a week of data. Colum A
has widget numbers that corrispond to three other columns that tell you how
many widgets as the other two colums also require totaling. If all of the
weekly data was in one sheet I could pivit the data. But the kicker her is
not only does the data reside in seperate sheets but the A colum is not in
the same order of each widget. So sheet number one may have widget 100 first
and sheet two may have widget 100 third. Whew...

"Harlan Grove" wrote:

Robert Lawrence wrote...
I would like to lookup and total values in multiple worksheets, The
worksheets have colums of labels and values and I want to total the
values of
each label in one worksheet.

Skimpy on the details, but if you want to sum conditionally over
several workbooks, you'll need to use some trickery. First, create a
list of the worksheets to process in a range somewhere and name that
range something like WSLST. Then use a formula like

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSLST&"'!A2:A1001") ,"="&X99,
INDIRECT("'"&WSLST&"'!C2:C1001")))

to sum values in C2:C1001 in the worksheets included in WSLST
corresponding to values in A2:A1001 equal to the value in cell X99 in
the worksheet containing this formula.

The trick here is that when you pass INDIRECT an array first argument,
it returns something that appears to function as an array of range
references. Most Excel functions can't handle that, but SUMIF and
COUNTIF can, and they return array results when passed apparent arrays
of range references.

"Robert Lawrence" wrote...
Let me try again: 5 worksheets each worksheet is a week of data. Colum
A has widget numbers that corrispond to three other columns that tell
you how many widgets as the other two colums also require totaling. If
all of the weekly data was in one sheet I could pivit the data. But the
kicker her is not only does the data reside in seperate sheets but the A
colum is not in the same order of each widget. So sheet number one may
have widget 100 first and sheet two may have widget 100 third. Whew...
....

If all you're doing is multiple worksheet conditional counting and summing,
try the formula I provided in my previous response. Record order in one
worksheet compared to that in any other worksheet is irrelevant.