Previous Topic Next Topic

Microsoft Excel

Closed
jms31280 - Jul 1, 2011 at 09:58 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 2, 2011 at 09:04 AM
Hello,
In column A I have specific numbers in each cell (i.e. A1= 1, A2=2, A3=3, A4=5), then in column B I have cells that have numbers as well (i.e. B1=1, B2=4, B3=18, B4=5). I want to count the number of cells that have matching numbers (i.e. A1 & B1 both have the number 1 in the cell, but A2 & B2 have different numbers. So for the above set the total number of matching cells is 2 (A1/B1 and A4/B4). How can I write a formula if I have hundreds of rows that I need to count the matches? Thank you so much in advance!


1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 2, 2011 at 09:04 AM
One way would be to use in third column
=IF(A1=B1,1,0)
and then you add the total at the end

other way can be to use SUMPRODUCT

=SUMPRODUCT(((A1:A4)=(B1:B4)) *1)

I am sure there are other ways too
0