sumproduct calculation does not calculate entire range

[John Gregory]

I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm>0), 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?

 

[jlclyde]

I have tweaked this formula to this point

=SUMPRODUCT((Zone=B8)*(O2scfm>0), 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)*(O2scfm>0)
* (O2scfm)) but that is just me.
Thanks,
Jay

 

[John Gregory]

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]

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






- 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

 

[John Gregory]

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.

 

[John Gregory]

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