Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF across multiple worksheets | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
multiple worksheets linked to a master worksheet | Excel Worksheet Functions | |||
Help with linked formulas & multiple worksheets | Excel Worksheet Functions | |||
Sumif across multiple worksheets | Excel Worksheet Functions |