sumproduct calculation does not calculate entire range
 

I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm)

and it works for the first few cells I want a sum in. It does not want to
calc the remainder of the range (Zone). I completed about 30 zones on my Room
Data tab, and went back to see if it worked, but it did the first few rows
correctly then nothing below that, why?

sumproduct calculation does not calculate entire range
 

On Sep 11, 1:07*pm, John Gregory
wrote:
I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm)

and it works for the first few cells I want a sum in. It does not want to
calc the remainder of the range (Zone). I completed about 30 zones on my Room
Data tab, and went back to see if it worked, but it did the first few rows
correctly then nothing below that, why?

Do you want it to always look at B8? If you do then you shoudl put $B
$8 instead of B8.
I woudl also change your formula to =SUMPRODUCT((Zone=$B$8)*(O2scfm0)
* (O2scfm)) but that is just me.
Thanks,
Jay

sumproduct calculation does not calculate entire range
 

No it will change as it goes down the list ie B9, B10 and so on and so on,
which seems to work fine. I modified it with )*(O2scfm) and it comes back
with a #VALUE error. On my 'Data Room' tab I have about 250 rows of
information that will contain room name, floor, riser, zone, area type (pull
down box), then several cells that I place quantities in, then another
similar set of cells which actuall VLOOKUP a range (SCFM) which will return a
number which calculated the quantity with the VLOOKUP result and places it in
these several cells. I then go back to my Summary Sheet and in cell B8 is
Zone 1 which will go all the way down to zone 250. I want the sumproduct to
look at 'Room Data' tab to find all zone 1 items and populate this row with
the appropriate results under each cell O2, MA, VAC.......
This formula I am using is not bringing information from 'Room Data' tab
over to Summary sheet from like the 4th row and down. Any ideas would be
great.
Thanks

"jlclyde" wrote:

On Sep 11, 1:07 pm, John Gregory
wrote:
I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm)

and it works for the first few cells I want a sum in. It does not want to
calc the remainder of the range (Zone). I completed about 30 zones on my Room
Data tab, and went back to see if it worked, but it did the first few rows
correctly then nothing below that, why?

Do you want it to always look at B8? If you do then you shoudl put $B
$8 instead of B8.
I woudl also change your formula to =SUMPRODUCT((Zone=$B$8)*(O2scfm0)
* (O2scfm)) but that is just me.
Thanks,
Jay

sumproduct calculation does not calculate entire range
 

On Sep 11, 1:34*pm, John Gregory
wrote:
No it will change as it goes down the list ie B9, B10 and so on and so on,
which seems to work fine. I modified it with )*(O2scfm) and it comes back
with a #VALUE error. On my 'Data Room' tab I have about 250 rows of
information that will contain room name, floor, riser, zone, area type (pull
down box), then several cells that I place quantities in, then another
similar set of cells which actuall VLOOKUP a range (SCFM) which will return a
number which calculated the quantity with the VLOOKUP result and places it in
these several cells. I then go back to my Summary Sheet and in cell B8 is
Zone 1 which will go all the way down to zone 250. I want the sumproduct to
look at 'Room Data' tab to find all zone 1 items and populate this row with
the appropriate results under each cell O2, MA, VAC.......
This formula I am using is not bringing information from 'Room Data' tab
over to Summary sheet from like the 4th row and down. Any ideas would be
great.
Thanks



"jlclyde" wrote:
On Sep 11, 1:07 pm, John Gregory
wrote:
I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm)

and it works for the first few cells I want a sum in. It does not want to
calc the remainder of the range (Zone). I completed about 30 zones on my Room
Data tab, and went back to see if it worked, but it did the first few rows
correctly then nothing below that, why?

Do you want it to always look at B8? *If you do then you shoudl put $B
$8 *instead of B8.
I woudl also change your formula to =SUMPRODUCT((Zone=$B$8)*(O2scfm0)
* (O2scfm)) but that is just me.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

Sumproduct needs to have ranges that are the same size to look at. If
your ranges area gettign bigger and your named ranges are not then
this is an issue. Either you will have to make your ranges flexible
or just put the range into the formula.
Jay

sumproduct calculation does not calculate entire range
 

the range is Zone, O2scfm, MAscfm and so on. So I am not sure how to correct
this. All the ranges are the same 250 cells in each of the range columns.

"jlclyde" wrote:

On Sep 11, 1:34 pm, John Gregory
wrote:
No it will change as it goes down the list ie B9, B10 and so on and so on,
which seems to work fine. I modified it with )*(O2scfm) and it comes back
with a #VALUE error. On my 'Data Room' tab I have about 250 rows of
information that will contain room name, floor, riser, zone, area type (pull
down box), then several cells that I place quantities in, then another
similar set of cells which actuall VLOOKUP a range (SCFM) which will return a
number which calculated the quantity with the VLOOKUP result and places it in
these several cells. I then go back to my Summary Sheet and in cell B8 is
Zone 1 which will go all the way down to zone 250. I want the sumproduct to
look at 'Room Data' tab to find all zone 1 items and populate this row with
the appropriate results under each cell O2, MA, VAC.......
This formula I am using is not bringing information from 'Room Data' tab
over to Summary sheet from like the 4th row and down. Any ideas would be
great.
Thanks



"jlclyde" wrote:
On Sep 11, 1:07 pm, John Gregory
wrote:
I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm)

and it works for the first few cells I want a sum in. It does not want to
calc the remainder of the range (Zone). I completed about 30 zones on my Room
Data tab, and went back to see if it worked, but it did the first few rows
correctly then nothing below that, why?

Do you want it to always look at B8? If you do then you shoudl put $B
$8 instead of B8.
I woudl also change your formula to =SUMPRODUCT((Zone=$B$8)*(O2scfm0)
* (O2scfm)) but that is just me.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

Sumproduct needs to have ranges that are the same size to look at. If
your ranges area gettign bigger and your named ranges are not then
this is an issue. Either you will have to make your ranges flexible
or just put the range into the formula.
Jay

sumproduct calculation does not calculate entire range
 

never mind I figuered it out.
Thanks for all your help

"jlclyde" wrote:

On Sep 11, 1:34 pm, John Gregory
wrote:
No it will change as it goes down the list ie B9, B10 and so on and so on,
which seems to work fine. I modified it with )*(O2scfm) and it comes back
with a #VALUE error. On my 'Data Room' tab I have about 250 rows of
information that will contain room name, floor, riser, zone, area type (pull
down box), then several cells that I place quantities in, then another
similar set of cells which actuall VLOOKUP a range (SCFM) which will return a
number which calculated the quantity with the VLOOKUP result and places it in
these several cells. I then go back to my Summary Sheet and in cell B8 is
Zone 1 which will go all the way down to zone 250. I want the sumproduct to
look at 'Room Data' tab to find all zone 1 items and populate this row with
the appropriate results under each cell O2, MA, VAC.......
This formula I am using is not bringing information from 'Room Data' tab
over to Summary sheet from like the 4th row and down. Any ideas would be
great.
Thanks



"jlclyde" wrote:
On Sep 11, 1:07 pm, John Gregory
wrote:
I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm)

and it works for the first few cells I want a sum in. It does not want to
calc the remainder of the range (Zone). I completed about 30 zones on my Room
Data tab, and went back to see if it worked, but it did the first few rows
correctly then nothing below that, why?

Do you want it to always look at B8? If you do then you shoudl put $B
$8 instead of B8.
I woudl also change your formula to =SUMPRODUCT((Zone=$B$8)*(O2scfm0)
* (O2scfm)) but that is just me.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

Sumproduct needs to have ranges that are the same size to look at. If
your ranges area gettign bigger and your named ranges are not then
this is an issue. Either you will have to make your ranges flexible
or just put the range into the formula.
Jay