Try
=SUMPRODUCT(SUMIF(INDIRECT("'[test.xls]"&I1:I3&"'!A2:A200"),"partnumber",IND
IRECT("'[test.xls]"&I1:I3&"'!B2:B200")))/SUMPRODUCT(COUNTIF(INDIRECT("'[test
..xls]"&I1:I3&"'!A2:A200"),"partnumber"))
where test.xls is the other workbook and I1:I3 is the range with the 3 sheet
names
and it is on the same sheet where you want this formula
note that the other workbook has to be open or else you will get an error
since you only have 3 sheets it could also be done by 6 sumproducts and work
with closed workbooks
=SUM(SUMPRODUCT(--('C:\Documents and Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet1'!$A$2:$A$200="partnumber"),'C:\Documents and
Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet1'!$B$2:$B$200),SUMPRODUCT(--('C:\Documents and
Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet2'!$A$2:$A$200="partnumber"),'C:\Documents and
Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet2'!$B$2:$B$200),SUMPRODUCT(--('C:\Documents and
Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet2'!$A$2:$A$200="partnumber"),'C:\Documents and
Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet2'!$B$2:$B$200))/SUM(SUMPRODUCT(--('C:\Documents
and Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet1'!$A$2:$A$200="partnumber")),SUMPRODUCT(--('C:\Doc
uments and Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet2'!$A$2:$A$200="partnumber")),SUMPRODUCT(--('C:\Doc
uments and Settings\Peo Sjoblom\My
Documents\[test.xls]Sheet3'!$A$2:$A$200="partnumber")))
however if you have the other workbook open when you put the formula there
then excel will put in the path
=SUM(SUMPRODUCT(--([test.xls]Sheet1!$A$2:$A$200="partnumber"),[test.xls]Shee
t1!$B$2:$B$200),SUMPRODUCT(--([test.xls]Sheet2!$A$2:$A$200="partnumber"),[te
st.xls]Sheet2!$B$2:$B$200),SUMPRODUCT(--([test.xls]Sheet2!$A$2:$A$200="partn
umber"),[test.xls]Sheet2!$B$2:$B$200))/SUM(SUMPRODUCT(--([test.xls]Sheet1!$A
$2:$A$200="partnumber")),SUMPRODUCT(--([test.xls]Sheet2!$A$2:$A$200="partnum
ber")),SUMPRODUCT(--([test.xls]Sheet3!$A$2:$A$200="partnumber")))
--
Regards,
Peo Sjoblom
vanity said:
First I want to Thank you Peo Sjoblom for your response.
I can't tell if I used this formula correctly, I got an error message
"your formula contains an invalid external reference to a work sheet"
Maybe I did not include in my problem that I am working with two work
books. One contains individual pages for companys listing part numbers,
quantity and price. In the second book I need to populate the total
quantity for inventory purposes. So I am trying to add all the common part
numbers from each page on the first book to get the total quantity of each
part. (the parts are sometimes reaccuring on a sheet). I need a formula as
I will need to add additional sheets in the future.
I also will be adding a page on the second work book to get an average
quantity and price to be able to determine profit.
