Adding worksheets using sumproduct function gives error

[Jolly]

Hi

I usually add together worksheets using:

= sum(start:end!a1)

which is fine but when I tried to use the sumproduct function:

=sumproduct(start:end!a1,start:end!y1)

where a1 has the gross data input and y1 has the working interest but this
gives me #ref error

I am trying to consolidate multiple worksheets which have gross data input
but I want to add the net data using the working interest in each sheet.

All the data input and working interest cells are in the same location in
each sheet.


I would appreciate any guidance to solve this problem
Many Thanks
Jolly

 

[T. Valko]

You can't use SUMPRODUCT like that.

Just do this:

=SUM(start:end!A1)*SUM(start:end!Y1)

 

[Jolly]

Thank you Biff but that does not give the correct answer.

It adds the two sets first and then multiplies whereas I need each item
multiplied first and then added.

Please help.

 

[T. Valko]

Oh, ok. I misunderstood.

Well, there's no easy way to do that with a single formula. It could be done
but the resulting formula would be long, complex and require you to list
your sheet names.

I would just use a formula on each sheet in the same cell like this:

Cell X1: =A1*Y1

Then you can do a simple sum like this:

=SUM(start:end!X1)

 

[Jolly]

Thanks T. Valko

The sheets have a lot of data from cells d10 to w300 which need to be
multplied to get the Net number.

Working out the net in the sheets would double up my data which I do not
need as I am only interested in the Net numbers on the case I am running.

 

[T. Valko]

The sheets have a lot of data from cells d10 to w300
which need to be multplied to get the Net number.

Hmmm...

That doesn't sound anything like this:

=sumproduct(start:end!a1,start:end!y1)

So, exactly what cells need to be calculated?

 

[Jolly]

Hi T

I am sorry I am not good at explaining but this is what I am trying to do:

I have

Columns A:B has dates
Columns D:m has production
Columns N:R has Expenditure
Columns S:V has Operating Costs
Column Y has the working Interest

All the above input data is in 10 to 215 where all the input data is gross.
Each sheet is a different case.

I need to add the data of the cases that are located between the START sheet
and END sheet. The added result should be the gross data inputs multiplied by
the working interest to give me a total net input data which I use to
calculate my cashflow forecast.

Hope that helps

Your help is much appreciated.

 

[T. Valko]

If you are able to/want to/open to the idea of using an add-in....

There is a free add-in called Morefunc.xll that has a funtion that will alow
you to use a 3D reference in SUMPRODUCT.

You can find it at one of these sites:

http://xcell05.free.fr/english/

http://xcell05.free.fr/morefunc/english/index.htm

Alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Then, you could use a formula like this:

=SUMPRODUCT(THREED(Sheet1:Sheet3!A1),THREED(Sheet1:Sheet3!B1))

That effectively does this:

=SUM{Sheet1A1*Sheet1B1,Sheet2A1*Sheet2B1,Sheet3A1*Sheet3B1)

If you can't use the add-in then it's kludge time!

 

[Jolly]

You are a STAR.

I typed the formula from scratch and it worked.

I have other users using this file so will they have problem running this
file?
Do they have to download the addin to their machine?
Thanks for all your help
Rita

 

[Jolly]

Hi T

I am sorry to trouble you again but my excel is running extremely slow since
adding the threed function.

My file is quite large and has a lot of sensitivities using tables. I have
turned off the tables calculation but it is still running very slow.

Is there any settings I can change to speed up the calculations?
Thanks
Rita

 

[T. Valko]

Well, at this point I don't know what to tell you.

The add-in can be embeded in the file so that others that may use it don't
need to have the add-in installed on their machine.

As for the slow performance, you may have to revert back to my original
suggestion of putting the individual formula on each sheet. If the add-in is
causing slow calculation the other kludge method formula will be even slower
because it uses volatile functions. I don't know if setting calculation to
manual and then only calculating when you need to would be a good option for
you.

For some general tips on efficiency see this:

http://www.decisionmodels.com/

 

[Jolly]

Hi T

How do you embed the add-in in the file?
At present I have it in tools/add-in and switched off the <morefun> while I
work on the file.

As to the speed, I have not managed to run the table sensitivities as the
model just stops responding.

I have checked the site in your email below but need more time to work
through what is best.

Rita

 

[T. Valko]

To embed the add-in:

When you installed the add-in it should have added a new item to your Tools
menu.

Tools>Morefunc>Embed Morefunc in the workbook

 

[Jolly]

GREAT!
Thank you so much for all your help.
Rita

To embed the add-in:

When you installed the add-in it should have added a new item to your Tools
menu.

Tools>Morefunc>Embed Morefunc in the workbook

 

[T. Valko]

You're welcome. Thanks for the feedback!