| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW TO CALCULATE AGES IN AN ENTIRE COLUMN?? | Excel Discussion (Misc queries) | |||
| HOW DO I CALCULATE THE NUMBER OF RESULTS FROM AN ENTIRE ROW | Excel Worksheet Functions | |||
| When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
| I would like to apply the same calculation to an entire row | Excel Worksheet Functions | |||
| How do I make an entire column do the same calculation across a r. | Excel Discussion (Misc queries) | |||
Linear Mode
