Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

I'd like to use array function to get conditional sum based on two columns. Given the example, for each ID I want to calculate the sum of value column if column B is -1

ID | B | value |
A1   1     2
A2   1     3
A3   1     4
A3  -1     5
A3  -1     1
A2   1     5
A1   1     1
A2  -1     2

The outcome should be

ID | value_total |
A1  1      0
A2  1      2
A3  1      6
share|improve this question

marked as duplicate by Excellll, Raystafarian, CharlieRB, nixda, Carl B Nov 5 '13 at 14:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

You want to sum col C for each unique value in col A when col B is -1? And you want a formula, not a macro? – Raystafarian Jul 31 '13 at 13:32
You probably want a sumifs – Raystafarian Jul 31 '13 at 13:39
@Raystafarian, that's it. Publish it as an answer and I'll accept it. – Karusmeister Jul 31 '13 at 14:09
@Excellll What is the point in having "Microsoft-excel" and "Microsoft-excel-2010" both as tag? You can have all Excel questions in your favorite list with *excel* as tag. Note the placeholders – nixda Nov 4 '13 at 20:28
@nixda See meta in a few places: – Excellll Nov 4 '13 at 21:53
up vote 2 down vote accepted

You'll want to use =sumifs() in excel versions later that 2007. Here is documentation


SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

=sumifs(C2:C9, A2:A9, "A#", B2:B9, "-1")

share|improve this answer

Not the answer you're looking for? Browse other questions tagged .