SUM & SUMIF formula problems

[Keith]

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.

 

[Elkar]

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

 

[T. Valko]

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)

 

[Keith]

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