If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#VALUE error on SUMIF formula linked to multiple worksheets
I am receiving a #VALUE error on a SUMIF formula that I have linked to an
external spreadsheet. If I only have the spreadsheet with the SUMIF formula open, I receive the #VALUE error. But if I open the external spreadsheet that it is linked to, then the #VALUE error disappears and the correct value displays. Would anyone know why this is happening? I would prefer to only have to open my one spreadsheet. Any suggestions would be great. Thanks, -- bknutzy |
#2
|
|||
|
|||
#VALUE error on SUMIF formula linked to multiple worksheets
There are some functions that will only work if the sending workbook is open.
=countif(), =sumif(), =indirect() are a few. But there can be replacement formulas that may work for you: =sumproduct() If you can get your formula to work when the sending workbook is open, post that working formula and maybe someone can give you an alternative. To get you started: =SUMPRODUCT(--('C:\My Documents\Excel\[book1.xls]Sheet1'!$A$1:$A$10="asdf"), 'C:\My Documents\Excel\[book1.xls]Sheet1'!$B$1:$B$10) is the equivalent of: =SUMif('C:\My Documents\Excel\[book1.xls]Sheet1'!$A$1:$A$10,"asdf", 'C:\My Documents\Excel\[book1.xls]Sheet1'!$B$1:$B$10) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html bknutzy wrote: I am receiving a #VALUE error on a SUMIF formula that I have linked to an external spreadsheet. If I only have the spreadsheet with the SUMIF formula open, I receive the #VALUE error. But if I open the external spreadsheet that it is linked to, then the #VALUE error disappears and the correct value displays. Would anyone know why this is happening? I would prefer to only have to open my one spreadsheet. Any suggestions would be great. Thanks, -- bknutzy -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|