Count Based on Multiple Criteria
The formulas in the following examples return a count based on multiple criteria. Note that the formula in the first example uses the function COUNTIFS, which is only available in Excel 2007 and later versions.
Using COUNTIFS in Excel 2007 and Later Versions
The
following formula counts the number of times a value in A2:A10
equals the value in D2, and the corresponding value in B2:B10 equals
the value in E2...
=COUNTIFS(A2:A10,D2,B2:B10,E2)
Based on the sample data, the formula returns 2.
Sample Workbook: Download
Using an Array Formula
The following formula counts the number of times a value in A2:A10 equals the value in D2, and the corresponding value in B2:B10 equals the value in E2...
=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2))
Sample Workbook: Download