asked on

SUMIF not working correctly

I have the formula below in a spreadsheet I'm working on. I'm using Excel 2010. This statement worked fine when I was total just one column. However, I'd like to modify this to add up multiple columns. I have a row ("E") that contains either "T7" or "T3". If the row is a T3, I'd like to total columns F,G,H, and I. This formula is only summing the first column (F). I've tried this with other columns (J,K,L, and M) with the same result.

Any ideas? I also tried without the absolutes, but that didn't work either.

=SUMIF($E$9:$E$202,"T3",$F$9:$I$202)

=SUMIF($E$9:$E$202,"T3",$F$9:$I$202)

Open in new window

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrew

I would suggest getting the total for each column as you have described, and then adding them together.

Or

You could have several sumif statements within the same formula i.e.

=sumif(e9:e202, "T3", f9) + sumif(.........)

Hope this helps

Andy

VenuChakkoth

Try this "=Sumif($E9$I$202,"T3",$F$9:$I:$202)" - The first argument of the Sumif formula should cover the entire range that falls under the calculation....

VenuChakkoth

Please ignore my earlier comment... Thats incorrect.

barry houdini

Hello VenuChakkoth, that won't work

There's a "one-to-one" relationship in SUMIF - F9 is summed if E9 is T3, G9 will only be added if F9 =T3.....

regards, barry

VenuChakkoth

Thanks Barry... Sorry, my bad! :(

Rob Henson

You can use an array formula:

=SUM(IF($E$9:$E$202="T3",$F$9:$I$202,0))

Confirm entry with Ctrl + Shift + Enter rather than just Enter. This will add curly brackets at start and finish so will end looking like:

{=SUM(IF($E$9:$E$202="T3",$F$9:$I$202,0))}

Thanks
Rob H

barry houdini

The array formula approach that Rob suggests is arguably the best - my SUMPRODUCT version will fail (#VALUE! error) if there is any text in the sum range - the array formula will ignore text.

regards, barry

jackadmin

ASKER

This worked exactly as I needed. Thanks!

byundt

I realize that the question has already been answered, but you can obtain text tolerant behavior with a tweak to barryhoudini's formula:
=SUMPRODUCT(($E$9:$E$202="T3")*{1,1,1,1},F$2:I$202)

jackadmin

ASKER

I like that the text throws an error since these cells should only have numeric data. I'm sure there are better ways to do it, but it does keep the user from putting non-numeric data in. Thanks everyone for their help, several good solutions.