Ok, I have the following as my SUMPRODUCTS function:

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$254="Fugro"))

And I'm runing 3 of these on different parts of a workbook. 3 Different
parts of the same workbook. But the problem is the 3rd time its doing
it, it refused to look at the unopened workbook.

Any suggestions?

J.

My bet is you have a spelling error in the third formula.

If you open the other workbook (nice for testing and nice for posting--since it
shortens the formulas), you may see the problem quicker.

If you don't see the problem, open the other workbook first, then copy each of
the 3 formulas to your followup post.

I like to build these formulas when I have the other workbook open. Then I can
start my formula:

=sumproduct(--(
And use the mouse to point at the other range.

Then I don't have to worry about any syntax rules at all. When I close the
workbook, excel will do the heavy lifting and fix all my formulas.



Egon wrote:

Ok, I have the following as my SUMPRODUCTS function:

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$254="Fugro"))

And I'm runing 3 of these on different parts of a workbook. 3 Different
parts of the same workbook. But the problem is the 3rd time its doing
it, it refused to look at the unopened workbook.

Any suggestions?

J.

--

Dave Peterson

Here are the three Formulas as shown with the file Open:

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul 05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

In this form, with the file open, all three work.

Here are the same three formulas when the file is closed.

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

When the file is closed, the last of these won't update. but the first
two update perfectly.

Think maybe Sumproduct is limited to the number of instances or
something? But that doesn't make sense because there are 4 sets of
these formulas and the first 2 ALWAYS work and the last always fails if
the file is closed.

J.

I don't see anything wrong with your formulas. There is a limit of 1024
characters in the formula (when measured in R1C1 notation). But you're not
close to that.

So you open the other workbook and you get one set of answers.

Then close the other workbook and that third formula evaluates to something else
(when you recalc)?

Just a wild guess...

Do you have calculation set for automatic (tools|options|calculation tab)

I don't have a real guess,

sorry.


Egon wrote:

Here are the three Formulas as shown with the file Open:

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul 05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

In this form, with the file open, all three work.

Here are the same three formulas when the file is closed.

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

When the file is closed, the last of these won't update. but the first
two update perfectly.

Think maybe Sumproduct is limited to the number of instances or
something? But that doesn't make sense because there are 4 sets of
these formulas and the first 2 ALWAYS work and the last always fails if
the file is closed.

J.

--

Dave Peterson

I have calculations set to Automatic, what I get when the workbook
isn't open is #N/A on the last formula.

Do you have any #n/a's in that range that you're trying to count?

(But then you'd get the same #n/a if the workbook is open or closed.)

Egon wrote:

I have calculations set to Automatic, what I get when the workbook
isn't open is #N/A on the last formula.

--

Dave Peterson

No, there aren't any #N/A's in the range that I'm look at, its all
company names. So I don't understand. Oddly enough, ONE of the
SUMPRODUCT functions now works this morning but the others are still
broken. I've built a macro that opens and closes all the files that are
references, but maybe its time to learn VBA and build a small app that
does the calculations for me.

Still not sure what excel is up to, thanks for your help Richard, if
you have any ideas I'm sure up for hearing them.

J.