Match and count

[fatnhappy]

I have a set of data in C:4:C99. I have another set of data in C104:R104. I want to count the number of times the data in C104:R104 matches the data in C4:C99 and display that number

The data in C4:C99 can be duplicates
The data in C104:R104 is unique and is not duplicated in this range.

I am sure this is a simple sumproduct, I am just not sure of the syntax.

Thanks

 

[Geoff H]

Try this formula in C105 and drag to R105

=COUNTIF($C4:$C99,C$104)

 

[dms37]

There may well be a more "correct" way, but the below will work...

=SUMPRODUCT((C4:C99=C104:R104)*1)

Hope I understood what you meant.

Dave

 

[fatnhappy]

OK, neither of those is what I am looking for. Perhaps a visual will work better. I would like to have a formula that compares a4:a10 with a13:f13 and return the number of times A4:A10 matches a13:F13. The answer I am looking for here is 4.

Book1
	A	B	C	D	E	F
1
2
3
4	red
5	fucia
6	green
7	beige
8	brown
9	yellow
10	red
11
12
13	blue	red	yellow	purple	green	white
Sheet1

 

[Aladin Akyurek]

=SUMPRODUCT(ISNUMBER(MATCH($A$4:$A$10,$A$13:$F$13,0))+0)

 

[fatnhappy]

Thanks aladin - as always you are right. Sumproducts confuse me sometimes, thanks for making it work.