Search code examples
google-sheetsarray-formulasgoogle-sheets-query

Getting a count of matched numbers in two series

My question is for Google Sheets I am aware of the countif function and know how to count frequency of one value in a series but I am struggling to find frequency of multiple values in two series. I have tried countifs, arrayformula, countif + sum with and without array formulas but unable to succeed.

example I have below series in E2:N2

31 32 35 45 49 55 57 66 72 75

and below series in O15:AH15

3 7 12 17 23 25 27 31 39 44 45 48 52 56 61 62 66 69 70 79

I want to see how many values matched and put that in cell A1 In the above example, 3 numbers matched so the value in A1 should be 3

I can do it with countif+countif+countif x 10 times but i wanted a very short formula.

Can someone give me some direction?

Thanks

Solution

  • Try 

    =SUMPRODUCT(TRANSPOSE(E2:N2)=O15:AH15)