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

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

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

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

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

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

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