2

How do I SUM a set of value from a columns, with 2 different columns as a condition for the calculation?

Month          Code           Value    
  4             A              50 
  4             A              20    
  4             B              20    
  5             A              30    
  5             B              20    
  5             B              30

The desired outcome should be a SUM of Value column that has the same Month and Code altogether:

 Month          Code         SumValue
   4             A              70
   4             B              20
   5             A              30
   5             B              50

I tried using IF, but IF could only take 1 parameter as the condition, is there any built in function of Ms Excel that could help with this?

Thank you very much for any help in advance.

Kerzoz
  • 331
  • 5
  • 18

2 Answers2

1

SUMIFS ... note the S. Couldn't be easier. :-) enter image description here

John Joseph
  • 1,003
  • 1
  • 10
  • 20
  • It wasn't me (since i am not 125 rep yet), but you could actually provide some clearer solution instead of looking down on the problem. So the downvote was justified – Kerzoz Feb 06 '18 at 06:43
1

SUMIFS is a function for summing cells where multiple other ranges match certain crtieria.

In the case of your example:

example SUMIFS

=SUMIFS(C2:C7,A2:A7,4,B2:B7,"A")

...sums values in C2:C7 where A2:A7 = 4 and B2:B7 = "A".

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • This answer won't copy properly if OP attempts to copy it to address the precise question because the references are not absolute and columns A and B are not even referenced. – John Joseph Feb 06 '18 at 03:46
  • @JohnJoseph -- I don't see that as a requirement in the OP's question. If the OP also needs a lesson on absolute/relative references then I'd be happy to add or point in the right direction. ...but thank you for expanding on your original answer fragment. – ashleedawg Feb 06 '18 at 04:03
  • @ashleedawg yes, it was not a requirement that i ask for a copy and paste ready solution, the problem was a toned-down version of the actual data after all (151584 row, 275 column) can't copy them all here. Even though not 100% accurate. You helped gave me insights tho, thanks! In the case, it should work with: `=SUMIFS($C$2:$C$7,$A$2:$A$7,4,$B$2:$B$7,"A")` Since the column ranges `A, B and C` won't be changing along with the parameters `4 and A` Please update your answer and that should be perfect :) – Kerzoz Feb 06 '18 at 06:39
  • You've got it :-) I will also add a note on 2-dimensional relative/absolute references. I actually had an explanation typed out recently... (*now where did I put it..*) – ashleedawg Feb 06 '18 at 06:45
  • @ErickVanhite -- in the meantime, you my find [my answer here](https://stackoverflow.com/a/48534323/8112776) also to be useful – ashleedawg Feb 06 '18 at 06:49