Alternative to SUMif when linking to closed workbooks

A

A Taxed Mind

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.
 
T

T. Valko

SUMPRODUCT should work:

=SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*your_path_sheet!D6:I40)
 
A

A Taxed Mind

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 said:
SUMPRODUCT should work:

=SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*your_path_sheet!D6:I40)

--
Biff
Microsoft Excel MVP


A Taxed Mind said:
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.
Click to expand...
Click to expand...
 
D

Dave Peterson

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.
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.
Click to expand...
 
A

A Taxed Mind

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 said:
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.
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.
Click to expand...
Click to expand...
 
T

T. Valko

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)*your_path_sheet!D6:I40)


--
Biff
Microsoft Excel MVP


A Taxed Mind said:
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 said:
SUMPRODUCT should work:

=SUMPRODUCT((your_path_sheet!C6:C40=sheet!C13)*your_path_sheet!D6:I40)

--
Biff
Microsoft Excel MVP


A Taxed Mind said:
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.
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

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 said:
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 said:
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.
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

You think that this will fix the value error?

T. Valko said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Never mind.

I didn't notice that the formula was using d6:i40.



T. Valko said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF linking to closed workbooks. 6
auto updating & linking 2 workbooks without opening the linked 6
VBA Sumif *********************** Help 2
VBA HELP! MsgBox & File & Sumif ****** 5
Refresh formula links to closed Workbooks 1
SUM in VBA, Msgbox 3
Closing Hidden Workbook when Active Workbook is Closed 5
Problem when copying a range from closed workbooks (ADO) 4

Top