| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alternative to SUMif when linking to closed workbooks
I know that I cannot use SUMif when linking to closed workbooks. I have the
following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
SUMPRODUCT should work:
=SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*you r_path_sheet!D6:I40) -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
Thanks for your help Biff, but am afraid I just can't get it to work.
I tried the following and just got error messages. SUMPRODUCT(('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13),*'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) =SUMPRODUCT(('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!C6:C40,)"="&'Maa. Tulot'!*C13),*'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!D6:I40) To be honest I still think I am not typing it correctly though. Can you see where I am going wrong? Thanks again. "T. Valko" wrote: SUMPRODUCT should work: =SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*you r_path_sheet!D6:I40) -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
First open your workbook. It'll make creating the formula easier and excel will
adjust the formula when you close the workbook. =sumproduct( --('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13), '[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) Notice that the comma ampersand is now a plain old equal sign. A Taxed Mind wrote: I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
Thanks Dave. Tried your idea:
=SUMPRODUCT(--('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13),'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) but still get #VALUE returned. Would there be a problem if the text to be matched or the text to be searched is actually a formula to return text from another location. It works fine with SUMIF, but of course only when the other workbooks are open, which is the problem. Off to bed, so will check for any replies in morning. Thanks for your help, I will be dreaming about this problem all night, my wife is right, I am crazy! "Dave Peterson" wrote: First open your workbook. It'll make creating the formula easier and excel will adjust the formula when you close the workbook. =sumproduct( --('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13), '[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) Notice that the comma ampersand is now a plain old equal sign. A Taxed Mind wrote: I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
Type it in *exactly* as I showed but insert your path and sheet names.
Better if you follow Dave's suggestion and open the source file then you can just point to the ranges and let Excel put all that path junk in for you. Notice in my formula there is no "="& stuff! =SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*you r_path_sheet!D6:I40) -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... Thanks for your help Biff, but am afraid I just can't get it to work. I tried the following and just got error messages. SUMPRODUCT(('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13),*'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) =SUMPRODUCT(('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!C6:C40,)"="&'Maa. Tulot'!*C13),*'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!D6:I40) To be honest I still think I am not typing it correctly though. Can you see where I am going wrong? Thanks again. "T. Valko" wrote: SUMPRODUCT should work: =SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*you r_path_sheet!D6:I40) -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
Remove the "--" and replace the comma with *.
=SUMPRODUCT(('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13)*'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) This is where I'd use named ranges! -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... Thanks Dave. Tried your idea: =SUMPRODUCT(--('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13),'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) but still get #VALUE returned. Would there be a problem if the text to be matched or the text to be searched is actually a formula to return text from another location. It works fine with SUMIF, but of course only when the other workbooks are open, which is the problem. Off to bed, so will check for any replies in morning. Thanks for your help, I will be dreaming about this problem all night, my wife is right, I am crazy! "Dave Peterson" wrote: First open your workbook. It'll make creating the formula easier and excel will adjust the formula when you close the workbook. =sumproduct( --('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13), '[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) Notice that the comma ampersand is now a plain old equal sign. A Taxed Mind wrote: I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
You think that this will fix the value error?
"T. Valko" wrote: Remove the "--" and replace the comma with *. =SUMPRODUCT(('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13)*'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) This is where I'd use named ranges! -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... Thanks Dave. Tried your idea: =SUMPRODUCT(--('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13),'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) but still get #VALUE returned. Would there be a problem if the text to be matched or the text to be searched is actually a formula to return text from another location. It works fine with SUMIF, but of course only when the other workbooks are open, which is the problem. Off to bed, so will check for any replies in morning. Thanks for your help, I will be dreaming about this problem all night, my wife is right, I am crazy! "Dave Peterson" wrote: First open your workbook. It'll make creating the formula easier and excel will adjust the formula when you close the workbook. =sumproduct( --('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13), '[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) Notice that the comma ampersand is now a plain old equal sign. A Taxed Mind wrote: I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alternative to SUMif when linking to closed workbooks
Never mind.
I didn't notice that the formula was using d6:i40. "T. Valko" wrote: Remove the "--" and replace the comma with *. =SUMPRODUCT(('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13)*'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) This is where I'd use named ranges! -- Biff Microsoft Excel MVP "A Taxed Mind" wrote in message ... Thanks Dave. Tried your idea: =SUMPRODUCT(--('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13),'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) but still get #VALUE returned. Would there be a problem if the text to be matched or the text to be searched is actually a formula to return text from another location. It works fine with SUMIF, but of course only when the other workbooks are open, which is the problem. Off to bed, so will check for any replies in morning. Thanks for your help, I will be dreaming about this problem all night, my wife is right, I am crazy! "Dave Peterson" wrote: First open your workbook. It'll make creating the formula easier and excel will adjust the formula when you close the workbook. =sumproduct( --('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13), '[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) Notice that the comma ampersand is now a plain old equal sign. A Taxed Mind wrote: I know that I cannot use SUMif when linking to closed workbooks. I have the following formula which only works when the workbook is open: =SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40) What could I do to make this work? I have tried both SUMproduct and SUM(if but excel returned errors both times, presumably because my typing is so poor. In case there is an easier way what I am trying to achieve is to match the text in the range on my summary sheet to the same text in the range in the annual sheets. When a match is found the adjacent value should be returned. Thanks in advance for your help. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF linking to closed workbooks. | Excel Discussion (Misc queries) | |||
| Linking to a closed workbook | Excel Worksheet Functions | |||
| alternative function to sumif | Excel Discussion (Misc queries) | |||
| SUMIF Alternative? | Excel Worksheet Functions | |||
| SUMIF Alternative? | Excel Discussion (Misc queries) | |||
Linear Mode
