Formula that counts occurrences in two columns where row values are equal

[travellerva]

I'm trying to find a formula that provides a count of the number of times in two columns that the values in corresponding rows are equal. I've tried various versions of COUNTIF without success. I realise this could be done by creating a "shadow" column that flags each row that's equal, then counting that column but I don't want to add a third column - even hidden. I also don't want to do it as a macro. Many thanks for all ideas.

[TABLE="width: 195"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]Problem: What is the formula in A1 that will give the result of the number of occasions the values in a row are equal?[/TD]
[/TR]
[TR]
[TD="colspan: 2"](2 in the example below)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]alpha[/TD]
[TD]45[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]beta[/TD]
[TD]36[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]gamma[/TD]
[TD]14[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]delta[/TD]
[TD]66[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]epsilon[/TD]
[TD]78[/TD]
[TD]78[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Is the First column ( alpha, beta, etc. ) a factor for the count ?

Edit, since I already worked it up even thou it's also posted below, I'll post it anyway, assuming your 1st column is Not a factor:

Book1
	A	B	C	D
1	2	alpha	45	23
2		beta	36	36
3		gamma	14	25
4		delta	66	45
5		epsilon	78	78
Sheet551

Cell Formulas
Range	Formula
A1	=SUMPRODUCT(--(C1:C5=D1:D5))

 

[Tetra201]

Try

=SUMPRODUCT(--(B2:B6=C2:C6))

 

[travellerva]

Many thanks. No, the first column is not a factor so your solution works fine.

Great help!

 

[travellerva]

Actually - I was too fast to reply! The formula you suggest actually yields twice the correct result. The easy way out is just =SUMPRODUCT(--(B2:B6=C2:C6))/2

 

[travellerva]

Nah - forget that. Your formula is absolutely correct. In my application of it I had several empty cells in the range that were counted as equal. Please ignore my last reply.

 

[jtakw]

If you might have Blanks within your range, you can use one of these instead:

Book1
	A	B	C	D
1	3	alpha	45	23
2	3	beta	36	36
3
4		gamma	14	25
5
6		delta	66	45
7			99	99
8		epsilon	78	78
Sheet551

Cell Formulas
Range	Formula
A1	=SUMPRODUCT((C1:C8=D1:D8)*(C1:D8<>""))/2
A2	=SUMPRODUCT((C1:C8=D1:D8)*(C1:C8<>"")*(D1:D8<>""))