Sumif Linking to Another Workbook error #VALUE!

[Tunde]

When I try to link a sumif to another workbook and I close the original
workbook I get #VALUE!, but as soon as I open the original workbook it works.
How do I stop this happening?

[Dave Peterson]

Use a different function...

Maybe =sum(if(...)) or =sumproduct() will work ok for you

Saved from a previous post (maybe you'll see how you can modify your existing
formula):

{=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green",
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))}

=sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"),
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10)

If this didn't help, post back with your existing formula.

by the way, the =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.)

--

Dave Peterson

[Tunde]

Hi Dave

I tried both =sum(if and =sumproduct and got #NUM! instead. Someone
suggested that the formula was too long, if that is so, is there anyway to
overcome this. Here's a copy of the formula

=SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation
2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E:$E,D$4,'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$J:$J)

Thank you.

Tunde

[RagDyer]

You probably received the #Num! error because Sumproduct doesn't allow
entire column references (E:E).

Try this, and adjust your rows accordingly:

=SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget

Preparation
2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E1:$E65000=D$4)*'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$J1:$J65000)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Tunde" <Tu...@discussions.microsoft.com> wrote in message
news:17BE7D9D-5933-456C...@microsoft.com...

[Tunde]

RD

I tried your suggestion and it came back with no value at all, and yes I did
try it in a cell where there should have been a value. I am getting a zero.

[Dave Peterson]

Open that other file and see what your formula and its results look like.

And post back the formula that you're currently using.

Notice that the =sum(if( function that I posted isn't the same as =sumif() that
you posted.

--

Dave Peterson

[Tunde]

I am attaching a complete list of what it looks like with all the different
formulas when open and when closed.

VIEW
external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N)

external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N)

external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N))

external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N))

external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N)

external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N)

external file open 0 =SUM(IF('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000))

external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000))

external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000)

external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000)

external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000)

external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager -

Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000)

Cheers,

[Dave Peterson]

Both =sum(if()) and =sumproduct() won't work with the whole column. So you have
to adjust your ranges if you want to get them to work.

And this confuses me:

external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$N:$N)

If the workbook is open, then you shouldn't be seeing the drive\path in the
filename.

--

Dave Peterson

[Tunde]

I have restricted the =sum(if()) and =sumproduct() to look from row 1 to
2000. The list I included shows the results with and without the restriction
of number of rows. What I mean by exerntal file open is that the workbook I
am linking to is open and external file closed means the workbook is closed.
I have to get my terminology right.

[Tunde]

I have restricted the column to rows 1 to 2000 and got zero as the value with
both =sum(if() and =sumproduct().

I converted the file path to text to show the results for the different
scenarios and to show the formula I used to get that result, in case I did
something wrong with one of my formulas.

[Dave Peterson]

I don't think your other workbook was open. The formula includes your path and
that doesn't appear when the other workbook is open.

So open your workbook with the formula.
Then open the other workbook.

Then back to one of the formulas. Copy it from the formula bar and paste it
into your response.

I really like the =sumproduct() version. If you have one of those, use that in
your example.

--

Dave Peterson

[Tunde]

When you make a path text it shows the formula but doesn't calculate it. I
just put an ' in front of = and then showed the exact path I was linking to.

These are the formulas that I have tried:

=SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation
2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N) This one works and gives the correct value
when the other workbook is open but as soon as I close it and update the link
it says #VALUE - it's my original query.

=SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation
2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N:$N)) This one shows #NUM! when the other
workbook is open but gives a value of Zero(0) when it is closed.

=SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget
Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager

-Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$N:$N) This one shows #NUM! when the other
workbook is open and also when it is closed.

=SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation
2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000)) This one shows zero value(0)
when the file is open and when it is closed.

=SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget
Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000) This one also shows zero (0) value
when the other workbook is open and closed.

=SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget
Preparation 2005 ~ 2006\[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager -
Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06

Estimates.xls]Sheet1'!$N$1:$N$2000) This one also shows zero value when
the other workbook is open or closed.

I am obviously missing something in my formulas, all the column and cell
references are correct so not really sure why none of these are working.

[Dave Peterson]

Let's try to make it a little simpler.

First, open both workbooks.

Second, don't convert the formula to text. Just copy it from the formula bar.
(Select the whole formula and rightclick|Copy.

Then paste into your post.

But choose only one formula--if we get one working, the others might become much
simpler.

And choose a formula like:

=SUMPRODUCT(
--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),
'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)

In fact, try this against a smaller range

=SUMPRODUCT(
--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2),
'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10)

And check your data to make sure that E1:E10 of that other worksheet actually
equals D2 of the formula worksheet.

Common problems are extra spaces (leading/trailing/embedded) or even numbers in
one worksheet, but text (that look like numbers) in the other.

This formula worked for me when the other workbook was open or closed.

======
Remember =sum(if()) and =sumproduct() cannot use a whole column.

<<snipped>>

[Tunde]

Hi Dave

I have made the range 1 to 200 as this will recognise all the rows that it's
looking at. I don't understand why it keeps bringing back a zero value. I
need it to look in one column in the other workbook and if the value matches
that of the workbook the formula is in then I want it to bring back the value
in another column in the other workbook. I need it to be a sum in case this
more than one row with the same value.

=SUMPRODUCT(--('[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$E$1:$E$200=D$4),'[Property Outgoings 2005-06
Estimates.xls]Sheet1'!$N$1:$N$200)

[Dave Peterson]

I'm gonna guess that the value in D$4 isn't the same as any of the values in
$e$1:$e:$200.

If you type in a test value in D4 and then copy|paste in $e$1 (and put 99 in
N1), do you get a nice answer.

I think it's a data matching problem.

Did you check for this?

> > Common problems are extra spaces (leading/trailing/embedded) or even
> > numbers in
> > one worksheet, but text (that look like numbers) in the other.

--

Dave Peterson

[Tunde]

The original Sumif is looking at the same cells and brings back a value when
the other workbook is open so that can't be it.

Woohoo, it appears that column e wasn't a value, I changed it to be a value
and it works. I can't believe it was such a simple fix. I didn't think the
normal =sumif() would work if it wasn't a value.

You're a genius.

Thanks heaps.

[Dave Peterson]

Whew!

Glad you found the problem and fixed it.

--

Dave Peterson