Count Based on Multiple Criteria

Counting Based on Multiple Criteria

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)

For Excel 2003 and prior versions, the following formula can be used...

=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2))

Based on the sample data, the formula returns 2.

Sample Workbook: Download