Adding worksheets using sumproduct function gives error
 

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

Adding worksheets using sumproduct function gives error
 

You can't use SUMPRODUCT like that.

Just do this:

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


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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

Adding worksheets using sumproduct function gives error
 

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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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
"Jolly" wrote:

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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/

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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
"Jolly" wrote:

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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" wrote:

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/

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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
"Jolly" wrote:

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

To embed the add-in:

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

ToolsMorefuncEmbed Morefunc in the workbook

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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/

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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
"Jolly" wrote:

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

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

"T. Valko" wrote:

To embed the add-in:

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

ToolsMorefuncEmbed Morefunc in the workbook

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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/

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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
"Jolly" wrote:

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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

Adding worksheets using sumproduct function gives error
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
GREAT!
Thank you so much for all your help.
Rita

"T. Valko" wrote:

To embed the add-in:

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

ToolsMorefuncEmbed Morefunc in the workbook

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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/

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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
"Jolly" wrote:

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

"T. Valko" wrote:

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/300...-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!


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Jolly" wrote in message
...
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.

"Jolly" wrote:

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