How can I make this Google Sheets formula more simple?-CodePudding

=SUM(COUNTIF(B3,C3), COUNTIF(B4,C4), COUNTIF(B5,C5), COUNTIF(B6,C6), COUNTIF(B7,C7), COUNTIF(B8,C8), COUNTIF(B9,C9), COUNTIF(B10,C10), COUNTIF(B11,C11), COUNTIF(B12,C12), COUNTIF(B13,C13), COUNTIF(B14,C14), COUNTIF(B15,C15), COUNTIF(B16,C16), COUNTIF(B17,C17), COUNTIF(B18,C18), COUNTIF(B19,C19), COUNTIF(B20,C20), COUNTIF(B21,C21), COUNTIF(B22,C22))/20*100

I'm attempting to match values between two columns and show the percentage that match. The ugly part is having to do a COUNTIF for each column comparison manually. Is there a way to just match B3:B22 against all of C3:C22 and get the count? My understanding of Google Sheets formulas is limited. Any help is appreciated! :-)

CodePudding user response：

Try

=SUM(ARRAYFORMULA(IF(B3:B22=C3:C22,1,0)))/20*100

CodePudding user response：

I'd think just a sumproduct would be simplest, no?

=SUMPRODUCT((B3:B22=C3:C22)*5)

CodePudding user response：

try:

=INDEX(SUM(1*(B3:B22=C3:C22))/20*100)