4

I need to sum the values from different sheets with two conditions.

The sample layout as following, I'd like to sum the values when Name=Andy,and Type=XXX ; add 35+43+23+35+18

Name      Type      Value
Andy       XXX       35
Bill       XXX       27
Gino       XXX       48
Bill       YYY       35
Andy       XXX       43
Bill       ZZZ       67
Gino       XXX       58
Bill       YYY       25
Andy       XXX       23
Andy       XXX       35
Bill       YYY       17
Gino       XXX       44
Bill       YYY       51
Andy       XXX       18
Bill       ZZZ       73

Please help with the formula. Truly appreciate!!

Raptor
  • 53,206
  • 45
  • 230
  • 366
user3654819
  • 45
  • 1
  • 4

1 Answers1

9

Definition of SUMIFS() is as follow:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Therefore, the formula you want is:

SUMIFS(C2:C20, A2:A20, "Andy", B2:B20, "XXX")

Reference: SUMIFS()

Raptor
  • 53,206
  • 45
  • 230
  • 366
  • Hi, Raptor! I got an additional question. How do I find the "Type" for "Bill" with his "Max Value"? In this case, the output should be ZZZ – user3654819 May 20 '14 at 09:36
  • You can use Pivot table (see [this](http://stackoverflow.com/a/14629740/188331) ) or `ARRAYFORMULA()`; the first one is easier – Raptor May 20 '14 at 10:37