Microsoft Excel

Closed
jms31280 - 1 Jul 2011 à 09:58
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 2 Jul 2011 à 09:04
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!


Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
2 Jul 2011 à 09:04
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