Solved

# sumif based on 3 columns

Posted on 2011-05-03
157 Views
Can you help me, i need to sum column H based on the conditionds of 3 other columns (a, c & i)

is this possible?
0
Question by:bryanscott53

LVL 33

Expert Comment

You can use SUMPRODUCT() function. Here's an article that shows how to use two conditions but it's the same logic for 3 conditions:

http://excel-user.blogspot.com/2009/10/sumproduct-sum-values-based-on.html

jppinto
0

LVL 50

Accepted Solution

Hello,

depending on your Excel version, the solution can differ. With 2007 or later, you can use SUMIFS. With earlier versions, you can use SUMPRODUCT.

Can you post a sample?

Excel 2007 or later
=Sumifs(H1:H10,A1:A10,"red",C1:C10,"blue",I1:I10,"green")

there are many ways to define the criteria. What do you want to compare?

Excel 2003 or earlier
=Sumproduct(--(A1:A10="red"),--(C1:C10="blue"),--(I1:I10="green"),H1:H10)

cheers, teylyn
0

LVL 33

Expert Comment

Here's a working example.

jppinto
SUMPRODUCT.xlsx
0

LVL 50

Expert Comment

jppinto, as I have said multiple times before: that article is a very bad example for the use of SUMPRODUCT. It does not illustrate at all how SUMPRODUCT can be used with multiple criteria, since you basically only return the value of one cell, and this could be done much more efficiently with Index/Match.
0

LVL 33

Expert Comment

That article intends to be just a basic approach to the SUMPRODUCT() function where I show just some basic examples for users that don't even know that SUMPRODUCT function exists, nothing more. It doesn't pretend to be a complete guide to SUMPRODUCT function.

0

Author Closing Comment

Teylyn thanks for your examples, I was able to follow them with ease.

jpinto, sorry but i did not understand how to use your examples but thanks for your response.
0

LVL 50

Expert Comment

0