In previous articles, I have talked about how to count unique/unique distinct/duplicates in a column/columns/range. In this article I count matching values using criteria.

count matching values

Formula in B16:

=SUMPRODUCT(--(A4=A8:A14)) + ENTER

count matching values1

Formula in B32:

=SUMPRODUCT(--(A20=A24:A30),--(B20=B24:B30)) + ENTER

count matching values2

Formula in B51:

=SUMPRODUCT(--(A37=A43:A49),--(B37=B43:B49), --(C37<C43:C49), --(C39>C43:C49)) + ENTER

Download excel file for this tutorial.

Count matching rows.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays