| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Value error
getting #Value error in cell if source file isn't open when using SUMIF
formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
Sumif needs to have the external file open, as does countif and several other
functions. I do not have a list of all of the which need to have the other file open. "Margie" wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
thanks for the information; doesn't make me happy, but it explains it anyway.
"bj" wrote: Sumif needs to have the external file open, as does countif and several other functions. I do not have a list of all of the which need to have the other file open. "Margie" wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
do you know if excel 2007 still requires that the source file be open?
"Margie" wrote: thanks for the information; doesn't make me happy, but it explains it anyway. "bj" wrote: Sumif needs to have the external file open, as does countif and several other functions. I do not have a list of all of the which need to have the other file open. "Margie" wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
Yes it does but sumproduct can do what you want, post the formula you have
when the source files are open, since Excel will put the path when it is closed it is easier (less to type etc. for us to help you For instance =SUMIF(A2:A1000,C2,B2:B1000) will sum B when A equals the value in C2 the equivalent using SUMPRODUCT would look like =SUMPRODUCT(--(A2:A1000=C2),B2:B1000) and it will work when the source book is closed, the drawback is that it is slower than SUMIF since it is a de facto array formula albeit not entered as one -- Regards, Peo Sjoblom "Margie" wrote in message ... do you know if excel 2007 still requires that the source file be open? "Margie" wrote: thanks for the information; doesn't make me happy, but it explains it anyway. "bj" wrote: Sumif needs to have the external file open, as does countif and several other functions. I do not have a list of all of the which need to have the other file open. "Margie" wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
I've tried it several ways and can't seem to be able to use this SUMPRODUCT -
either getting wrong numbers or #NUM error. my orig formula looks at a table and finds a value then only would add up if it found that value on the second table the $"s in the total column. It seems to be adding up the entire column or giving me the error. suggestions? "Peo Sjoblom" wrote: Yes it does but sumproduct can do what you want, post the formula you have when the source files are open, since Excel will put the path when it is closed it is easier (less to type etc. for us to help you For instance =SUMIF(A2:A1000,C2,B2:B1000) will sum B when A equals the value in C2 the equivalent using SUMPRODUCT would look like =SUMPRODUCT(--(A2:A1000=C2),B2:B1000) and it will work when the source book is closed, the drawback is that it is slower than SUMIF since it is a de facto array formula albeit not entered as one -- Regards, Peo Sjoblom "Margie" wrote in message ... do you know if excel 2007 still requires that the source file be open? "Margie" wrote: thanks for the information; doesn't make me happy, but it explains it anyway. "bj" wrote: Sumif needs to have the external file open, as does countif and several other functions. I do not have a list of all of the which need to have the other file open. "Margie" wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
You could convert to an array formula:
=sum(if(....)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Or you could use =sumproduct() =sumproduct(--(somerange=somevalue)) You can't use the whole column with this, too (until xl2007). Margie wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
I tried that, but it gives me the entire files total (4.4million instead of
the line that it is suppose to look for a match on of only 10k). I only have excel 2003 so i guess this won't work for me. I was also told that certain functions require you to have the source file open and SUMIF and COUNTIF are some of them that require this. does 2007 require that as well? Of course that doesn't solve my problem since opening the source file each time this file is used is too much add'l work for the users. "Dave Peterson" wrote: You could convert to an array formula: =sum(if(....)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Or you could use =sumproduct() =sumproduct(--(somerange=somevalue)) You can't use the whole column with this, too (until xl2007). Margie wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Value error
This technique will work in xl2003. But you can't use whole columns.
I don't understand what you mean about it giving you the entire file. This =sum(if()), =sumif(), and =sumproduct() will add all the values that meet the criteria--not the entire file and not just one line. Margie wrote: I tried that, but it gives me the entire files total (4.4million instead of the line that it is suppose to look for a match on of only 10k). I only have excel 2003 so i guess this won't work for me. I was also told that certain functions require you to have the source file open and SUMIF and COUNTIF are some of them that require this. does 2007 require that as well? Of course that doesn't solve my problem since opening the source file each time this file is used is too much add'l work for the users. "Dave Peterson" wrote: You could convert to an array formula: =sum(if(....)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Or you could use =sumproduct() =sumproduct(--(somerange=somevalue)) You can't use the whole column with this, too (until xl2007). Margie wrote: getting #Value error in cell if source file isn't open when using SUMIF formula. Once file is opened the correct $$/amounts appear and there are no errors. why am i getting value errors using this formula??? see example of formula i am questioning below. =SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23 Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL) -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error with SUMIF but not SUM(IF) | Excel Worksheet Functions | |||
| Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) | |||
| If and nested Sumif error | Excel Worksheet Functions | |||
| countif / sumif function error | Excel Worksheet Functions | |||
| Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) | |||
Hybrid Mode
