Solved

SUMIF not working correctly

Posted on 2011-10-20
208 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)
``````
0
Question by:jackadmin
11 Comments

LVL 50

Accepted Solution

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

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

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

Please ignore my earlier comment... Thats incorrect.
0

LVL 50

Expert Comment

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

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

LVL 31

Expert Comment

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

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

This worked exactly as I needed.  Thanks!
0

LVL 80

Expert Comment

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

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

Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

Featured Post

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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

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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!