?
Solved

SUMIF not working correctly

Posted on 2011-10-20
11
Medium Priority
?
236 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:jackadmin
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 50

Accepted Solution

by:
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

by:andymacf
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

by:VenuChakkoth
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37001538
Please ignore my earlier comment... Thats incorrect.
0
 
LVL 50

Expert Comment

by:barry houdini
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

by:VenuChakkoth
ID: 37001583
Thanks Barry... Sorry, my bad! :(
0
 
LVL 34

Expert Comment

by:Rob Henson
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

by:barry houdini
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

by:jackadmin
ID: 37001711
This worked exactly as I needed.  Thanks!
0
 
LVL 81

Expert Comment

by:byundt
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

by:jackadmin
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question