Sum of rows conditional on two conditions

0

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

Karusmeister

Posted 2013-07-31T13:27:44.037

Reputation: 209

Question was closed 2013-11-05T14:05:52.660

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 – 2013-07-31T13:32:45.177

1

You probably want a sumifs

– Raystafarian – 2013-07-31T13:39:24.900

@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 – 2013-11-04T20:28:45.733

@nixda See meta in a few places: http://meta.superuser.com/q/7105/76571 http://meta.superuser.com/q/1261/76571

– Excellll – 2013-11-04T21:53:23.593

@Excellll Thank you, I already know them. In fact I thought a lot about them. I realized that there is confusion about this topic, so I try to convince others to remove redundant tags too. In this specific question, I may have chosen the wrong one since its more of a general Excel question. Please note that I would never remove e.g "debian" and let "linux" in place. This is only about Excel and its versions – nixda – 2013-11-04T22:04:11.577

@nixda ha I didn't look to see that your previous edit was to remove the general tag. I just happened to see it on the front page and I thought I'd fix it. Anyhow, I think the general tag is useful because it lets users of other versions know that the solutions here should apply to them as well. For instance I wouldn't want a novice Excel 2007 user to think SUMIFS was new to Excel 2010 and unavailable to them based on the tagging of the question. I suggest posting on Meta if you think Excel tagging should work a certain way. – Excellll – 2013-11-04T22:12:53.810

Answers

2

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

Syntax:

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

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

Raystafarian

Posted 2013-07-31T13:27:44.037

Reputation: 20 384

Asked: 2013-07-31T13:27:44.037

Viewed: 140 times

Active: 2013-11-04T20:09:53.333