Forum Discussion
melissach
Apr 10, 2024Copper Contributor
HELP NEEDED! Formula needed for word matches in three different columns
Hello!
Need help with counting how many time three words match in the same line in three different columns.
Thank you!
Words are entire cell values or part of the text?
Are words predefined or you would like to know how many cells have exactly the same text?
- melissachCopper Contributor
- Patrick2788Silver Contributor
There are some details that I don't know, but I will create this demo going by these rules:
-Excel 365
-a given word must appear at least 3 times in a given row
-exact matches on words, no partials
=LET( target, 3, cols, COLUMNS(matrix), multiplier, SEQUENCE(cols, , 1, 0), BYROW( colors, LAMBDA(each_color, LET( vector, MMULT(N(matrix = each_color), multiplier), SUM(N(vector >= target)) ) ) ) )
- melissachCopper Contributor
- Patrick2788Silver Contributor
Thank you for adding a sample workbook. You can do this with COUNTIFS or a pivot.
=COUNTIFS(Colour, G3:G5, Flower, H1:M1, Event, H2:M2)