Solved

# SUMIF not working correctly

Posted on 2011-10-20
Medium Priority
236 Views
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)
``````
0
• 3
• 3
• 2
• +3

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 37001513
You can(t use SUMIF for multiple columns, try SUMPRODUCT

=SUMPRODUCT((\$E\$9:\$E\$202="T3")*F\$9:\$I\$202)

regards, barry
0

LVL 7

Expert Comment

ID: 37001523
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
0

LVL 1

Expert Comment

ID: 37001529
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....
0

LVL 1

Expert Comment

ID: 37001538
Please ignore my earlier comment... Thats incorrect.
0

LVL 50

Expert Comment

ID: 37001556
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
0

LVL 1

Expert Comment

ID: 37001583
Thanks Barry... Sorry, my bad! :(
0

LVL 34

Expert Comment

ID: 37001595
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
0

LVL 50

Expert Comment

ID: 37001708
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
0

Author Closing Comment

ID: 37001711
This worked exactly as I needed.  Thanks!
0

LVL 81

Expert Comment

ID: 37001972
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)
0

Author Comment

ID: 37002445
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usā¦
###### Suggested Courses
Course of the Month13 days, 12 hours left to enroll