[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

sumif based on 3 columns

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
bryanscott53
Asked:
bryanscott53
  • 3
  • 3
1 Solution
 
jppintoCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jppintoCommented:
Here's a working example.

jppinto
SUMPRODUCT.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jppintoCommented:
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.

But thanks for your input...
0
 
bryanscott53Author Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Glad it worked for you. Thanks for the grade!
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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now