I have this range C1:Q2000, each row contains 15 numeric values, and AA1:AT1 with 20 numeric values.
I need to count how many rows in C1:Q2000 contain 15 matches with AA1:AT1
This is the formula I use to count matches
=SUMPRODUCT(COUNTIF(C1:Q1, AA1:AT1))
But would only count one row at a time and I don't seem to come up with an idea of how to sum the counts of all the 2000 rows and only if there are 15 matching values.
All I know is I could make a column with the count of each row, and count how many times the result was 15, that would do it, but I have to repeat this from AA1:AT1 until AA3000:AT3000 and not only look for 15 matches but also 0,1,2... matches up to 15. It gets complicated... I'm looking if there is a simpler way.
I have found some sheets that do this with a smaller amount of numbers, but all use vba. Is there a way of doing this only using formulas?
Thanks for your help!
Bookmarks