Error on sumif formula that links to another workbook

[ajsmith]

The idea of this is to display summary data from another spreadsheed based on criteria selected in this spreadsheet. This is important, because the formula is used to get data from several sources that cannot be held on the same spreadsheet for security reasons, but summary information is required.

The formula is -

=SUMIF('path[MySpreadsheet.xls]Data'!$N:$N,$J$3&$J$4,'path[MySpreadsheet.xls]Data'!$F:$F)

It works fine if I have all the linked spreadsheets open, but if I open the summary sheet on its own and update the links I get the #VALUE error.

Does anyone have any ideas on solution or workarounds?

[ This Message was edited by: ajsmith on 2002-05-16 04:44 ]

[Dave Patton]

[quote]
On 2002-05-16 04:42, ajsmith wrote:
The idea of this is to display summary data from another spreadsheed based on criteria selected in this spreadsheet. This is important, because the formula is used to get data from several sources that cannot be held on the same spreadsheet for security reasons, but summary information is required.

try SumProduct
- use defined range or a dynamic range (Do not use entire columns)

=SUMPRODUCT(('E:Data02Tech02[mySpreadsheet.xls]Data'!$N$1:$N$12=J3)*('E:Data02Tech02[mySpreadsheet.xls]Data'!$F$1:$F$12))

[ajsmith]

[quote]
On 2002-05-16 10:49, Dave Patton wrote:

On 2002-05-16 04:42, ajsmith wrote:
The idea of this is to display summary data from another spreadsheed based on criteria selected in this spreadsheet. This is important, because the formula is used to get data from several sources that cannot be held on the same spreadsheet for security reasons, but summary information is required.

try SumProduct
- use defined range or a dynamic range (Do not use entire columns)

=SUMPRODUCT(('E:Data02Tech02[mySpreadsheet.xls]Data'!$N$1:$N$12=J3)*('E:Data02Tech02[mySpreadsheet.xls]Data'!$F$1:$F$12))

[ajsmith]

I do get a result using SUMPRODUCT, but it's not the result I need. I am not sure what it's doing, but it aint what I want (sum of the values where criteria is met).
Can I get SUMPRODUCT to produce the result I want?

[Dave Patton]

[quote]
On 2002-05-16 14:23, ajsmith wrote:
I do get a result using SUMPRODUCT, but it's not the result I need. I am not sure what it's doing, but it aint what I want (sum of the values where criteria is met).
Can I get SUMPRODUCT to produce the result I want?

SumProduct will probably provide the sum that you require. You did not provide information so that I can extend the formula.

Could you supply a concise example and indicate the expected result?

consider the following:
=SUMPRODUCT(('E:Data02Tech02[mySpreadsheet.xls]Data'!$N$1:$N$12={"A","B"})*('E:Data02Tech02[mySpreadsheet.xls]Data'!$F$1:$F$12))

or

=SUMPRODUCT((('E:Data02Tech02[mySpreadsheet.xls]Data'!$N$1:$N$12=J3)+('E:Data02Tech02[mySpreadsheet.xls]Data'!$N$1:$N$12=J4))*('E:Data02Tech02[mySpreadsheet.xls]Data'!$F$1:$F$12))

REVISE the path and range to fit your information.

[ajsmith]

Here goes -

The detail sheet gives values of business in the pipeline that an agent fills in. He enters the date of the prospect, a description and the value. This date is then split in hidden cells to extract the year and the month (e.g. 2002 and 3). If business does not come through he enters NTU (not taken up) in another cell on the same row. I have another hidden column that uses the following formula to display the concatonated numeric date only if NTU is entered -
=IF(H2="NTU",J2&I2,"")
I can now identify all NTU for a given year / month.

The summary sheet user wants an idea how much business is being lost out of these leads for a particular month. I have set up the summary sheet so that he can dynamically select a year and month in different cells from drop down lists. This then privides the necassary info to sum matching NTU values in the detail sheet.

Thanks for your patience, I hope this makes sense. I would be happy to email the stuff to you if you have the time and it will help.

[Yogi Anand]

On 2002-05-16 14:23, ajsmith wrote:
I do get a result using SUMPRODUCT, but it's not the result I need. I am not sure what it's doing, but it aint what I want (sum of the values where criteria is met).
Can I get SUMPRODUCT to produce the result I want?

SumIf function does not appear to work with the data in closed workbooks. But utilizing SumProduct function does work even when the workbook with data is/are closed.
Now in regard to making SumProduct function produce the result you want, I suggest you first use develop the SumProduct function formulation in the same workbook where the data resides -- so now you know that you got the formulation right to accomplish what you want,
then go ahead and use the formulation in your new workbook to make it work when the workbook with data is closed.

I hope it makes sense -- otherwise post back with some explanation and let us take it from there.

Regards!

[Asala42]

Hey,

Here's a stab:

=SUMPRODUCT(('path[MySpreadsheet.xls]Data'!$N:$N=$J$3&$J$4)*('path[MySpreadsheet.xls]Data'!$F:$F))

Your criteria is a literal concatenation of J3&J4 correct?

Adam

[ajsmith]

On 2002-05-16 15:00, Asala42 wrote:
Hey,

Here's a stab:

=SUMPRODUCT(('path[MySpreadsheet.xls]Data'!$N:$N=$J$3&$J$4)*('path[MySpreadsheet.xls]Data'!$F:$F))

Your criteria is a literal concatenation of J3&J4 correct?

Adam

Hi Adam

Concatonation is literal, and that's the exact flavour of the sumproduct formula I tried 1st time round - doesn't seem to give me the correct result. Thanks for the effort, any other ideas appreciated. I though about the possibility of automatically opening all the related spreadsheets in the background (hidden?) when the spreadsheet is opened and then closing them all when it's closed. I don't know if it's possible and what the implications would be if the detail sheets were already open or if excel throws up a load of annoying "do you want to save changes" messages for the detail sheets when it tries to close them. This scenario seems far from ideal.

Ant

[Aladin Akyurek]

On 2002-05-16 15:13, ajsmith wrote:

Hi Adam

Concatonation is literal, and that's the exact flavour of the sumproduct formula I tried 1st time round - doesn't seem to give me the correct result. Thanks for the effort, any other ideas appreciated. I though about the possibility of automatically opening all the related spreadsheets in the background (hidden?) when the spreadsheet is opened and then closing them all when it's closed. I don't know if it's possible and what the implications would be if the detail sheets were already open or if excel throws up a load of annoying "do you want to save changes" messages for the detail sheets when it tries to close them. This scenario seems far from ideal.

Ant

If

=SUMIF('path[MySpreadsheet.xls]Data'!$N:$N,$J$3&$J$4,'path[MySpreadsheet.xls]Data'!$F:$F)

works,

=SUMPRODUCT(('path[MySpreadsheet.xls]Data'!$N$2:$N$400=$J$3&$J$4)*('path[MySpreadsheet.xls]Data'!$F$2:$F$400))

will also work (a point Dave Patton made).

Adjust the ranges to suit. This formula cannot work with whole columns.



[ This Message was edited by: Aladin Akyurek on 2002-05-16 15:20 ]