I need an excel formula that will return the number of cells in a
range that are equal to another range.  For example:
A   B   C
10      10  
    15  
    25  25
12      12
15      15
    19  19
42      42
19
76
99      99
87      

The only condition is that I don't want to have formulas in multiple cells.

The question then is how many cells in column C equal column A(5)? and
the same for B(2).

I've played around with various COUNTA and IF arrangements, and can't
seem to get the correct formula.

---

Clarification of Question by zpilto-ga on 11 Feb 2006 13:24 PST

To clarify:

I'm looking for one formula that will return the number of cells in C
with a value equal to the cell in colum A on the same row.

How about this?

=SUMPRODUCT(N(A1:A11=C1:C11),N(NOT(ISBLANK(A1:A11))))

This is for matches in non blank cells between columns A and C on the same row.

I don't have a copy of excel handy to verify this but according to this page:
http://www.cpearson.com/excel/excelF.htm
the following should work:
=SUM((C1:C11>=A1)*(C1:C11<=A1))

Actually, to simplify, I think you can just do this:
=SUM(C1:C11=A1)

---

How about this?

=SUMPRODUCT(N(A1:A11=C1:C11),N(NOT(ISBLANK(A1:A11))))

This is for matches in non blank cells between columns A and C on the same row.

---


That works great! Thanks.