Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM & SUMIF formula problems
I have a portion of a spreadsheet with 13 columns and 119 rows of data (from
BE5 to BQ123 - with each row being designated as "HOV Freeway" "Freeway" or "Arterial" in column C). I tried to perform a simple sum on this data IF the row is a "HOV Freeway". So the formula I used was: =(SUMIF(C5:C123,"HOV Freeway",BE5:BQ123))... I didn't get any type of error, excel seemed to accept the formula and gave me a value of 24889.6, but that value is just the Sum of BE5:BE123. When doing a quick check, the real value should be 281261.0... not even close to what excel calculated for me. My question is, why didn't I get an error if it is only going to sum one column, instead of the 13 columns I thought I was asking for in the formula? Is there a way to sum all of those columns and rows, using SUMIF? Does the range you specify for SUMIF correlate to the sum_range (ie, if the 'if' range is only 1 column, the sum_range can only be returned for 1 column)? I really need this to work for 13 columns. Thanks a lot for the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM & SUMIF formula problems
With SUMIF, your criteria range and your sum range must be the same size.
What if you added a total column, say in column BR? This way all of your data to sum would be in one column. So, in BR5, you'd enter the formula =SUM(BE5:BQ5) then copy down through row 123. Then your sumif formula would be: =SUMIF(C5:C123,"HOV Freeway",BR5:BR123) HTH, Elkar "Keith" wrote: I have a portion of a spreadsheet with 13 columns and 119 rows of data (from BE5 to BQ123 - with each row being designated as "HOV Freeway" "Freeway" or "Arterial" in column C). I tried to perform a simple sum on this data IF the row is a "HOV Freeway". So the formula I used was: =(SUMIF(C5:C123,"HOV Freeway",BE5:BQ123))... I didn't get any type of error, excel seemed to accept the formula and gave me a value of 24889.6, but that value is just the Sum of BE5:BE123. When doing a quick check, the real value should be 281261.0... not even close to what excel calculated for me. My question is, why didn't I get an error if it is only going to sum one column, instead of the 13 columns I thought I was asking for in the formula? Is there a way to sum all of those columns and rows, using SUMIF? Does the range you specify for SUMIF correlate to the sum_range (ie, if the 'if' range is only 1 column, the sum_range can only be returned for 1 column)? I really need this to work for 13 columns. Thanks a lot for the help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM & SUMIF formula problems
Does the range you specify for SUMIF correlate to
the sum_range (ie, if the 'if' range is only 1 column, the sum_range can only be returned for 1 column)? Yes, that's how it works. Try it like this: =SUMPRODUCT((C5:C123="HOV Freeway")*BE5:BQ123) -- Biff Microsoft Excel MVP "Keith" wrote in message ... I have a portion of a spreadsheet with 13 columns and 119 rows of data (from BE5 to BQ123 - with each row being designated as "HOV Freeway" "Freeway" or "Arterial" in column C). I tried to perform a simple sum on this data IF the row is a "HOV Freeway". So the formula I used was: =(SUMIF(C5:C123,"HOV Freeway",BE5:BQ123))... I didn't get any type of error, excel seemed to accept the formula and gave me a value of 24889.6, but that value is just the Sum of BE5:BE123. When doing a quick check, the real value should be 281261.0... not even close to what excel calculated for me. My question is, why didn't I get an error if it is only going to sum one column, instead of the 13 columns I thought I was asking for in the formula? Is there a way to sum all of those columns and rows, using SUMIF? Does the range you specify for SUMIF correlate to the sum_range (ie, if the 'if' range is only 1 column, the sum_range can only be returned for 1 column)? I really need this to work for 13 columns. Thanks a lot for the help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM & SUMIF formula problems
Thanks for the help,
I think i will add a total column, but the way I have it set up it would be inconvienent, but that will work. It is just odd that excel doesn't return an error. Thanks again, Keith "Elkar" wrote: With SUMIF, your criteria range and your sum range must be the same size. What if you added a total column, say in column BR? This way all of your data to sum would be in one column. So, in BR5, you'd enter the formula =SUM(BE5:BQ5) then copy down through row 123. Then your sumif formula would be: =SUMIF(C5:C123,"HOV Freeway",BR5:BR123) HTH, Elkar "Keith" wrote: I have a portion of a spreadsheet with 13 columns and 119 rows of data (from BE5 to BQ123 - with each row being designated as "HOV Freeway" "Freeway" or "Arterial" in column C). I tried to perform a simple sum on this data IF the row is a "HOV Freeway". So the formula I used was: =(SUMIF(C5:C123,"HOV Freeway",BE5:BQ123))... I didn't get any type of error, excel seemed to accept the formula and gave me a value of 24889.6, but that value is just the Sum of BE5:BE123. When doing a quick check, the real value should be 281261.0... not even close to what excel calculated for me. My question is, why didn't I get an error if it is only going to sum one column, instead of the 13 columns I thought I was asking for in the formula? Is there a way to sum all of those columns and rows, using SUMIF? Does the range you specify for SUMIF correlate to the sum_range (ie, if the 'if' range is only 1 column, the sum_range can only be returned for 1 column)? I really need this to work for 13 columns. Thanks a lot for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif problems | Excel Discussion (Misc queries) | |||
SUMIF problems | Excel Worksheet Functions | |||
sumif problems | Excel Discussion (Misc queries) | |||
Problems with SUMIF() | Excel Worksheet Functions | |||
Sumif Problems | Excel Discussion (Misc queries) |