Does anyone know the best way to find the number of cells in 2 columns that match? The cells would be in the same row if there was a match.

Right now I have a long equation that says =Sum(countif(A1,B1), countif(A2,B2), countif(A3,C3)...)
I know there is an easier way and would appreciate any suggestions.

9/11/2010 1:04:14 PM

not sure on a single cell solution right now, but off the top of my head, you could do:

in col c: =A1=B1 and so on down the column. then in one cell =countif(C1:C10,"TRUE")

if you need the total number of cells matching, just multiple that by two.

9/11/2010 2:03:00 PM

could use vlookup

9/11/2010 3:16:58 PM

A vlookup will not work for that. The easiest way would be to add a third column of if(A1=B1,1,0) and then do a sum in the row at the bottom of that column.


Oh, I should have read the thread, price got it.

[Edited on September 11, 2010 at 4:15 PM. Reason : .]

9/11/2010 4:14:48 PM

Sure a vlookup would work, just drag it down and sum at the bottom

9/11/2010 4:16:45 PM

You are incorrect. Go study how vlookup works and try posting again.

9/11/2010 4:27:09 PM

yeh, I don't see how a vlookup would work for this... unless you have some long-ass convoluted formula with a lot of other formulas/functions in it... still don't see that it would be possible though.

9/11/2010 4:48:59 PM