Sum Based on Multiple Criteria
The formulas in the following examples return a sum based on multiple criteria. Note that the formula in the first example uses the function SUMIFS, which is only available in Excel 2007 and later versions.
Using SUMIFS in Excel 2007 and Later Versions
The following formula returns the sum of C2:C10, where the corresponding value in A2:A10 equals the value in E2, and the correspondng value in B2:B10 equals the value in F2...
=SUMIFS(C2:C10,A2:A10,E2,B2:B10,F2)
Based on the sample data, the formula returns 10.
Sample Workbook: Download
Using an Array Formula
The following formula returns the sum of C2:C10, where the corresponding value in A2:A10 equals the value in E2, and the correspondng value in B2:B10 equals the value in F2...
=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2),C2:C10)
Sample Workbook: Download