HELP NEEDED! Formula needed for word matches in three different columns

Copper Contributor



Need help with counting how many time three words match in the same line in three different columns. 


Thank you!

16 Replies


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?


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




    target, 3,
    cols, COLUMNS(matrix),
    multiplier, SEQUENCE(cols, , 1, 0),
                vector, MMULT(N(matrix = each_color), multiplier),
                SUM(N(vector >= target))






@Sergei Baklan 




Words are the entire cell value. Here is an example.


Thanks! :)



Not quite what I was looking for. Here is an example.

Thanks! :)


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)


Doesn't work on my example...


Are you getting a #NAME? error?


Here's the formula without named items:



Still doesn't work... It's not a #Name error but a formula error. I've switch the countifs in French for my computer but still doesn't work

It might help if you provide the formula or the error.

@Patrick2788 Sure, here you go! Sorry, had to change my Excel to English. 



You may be in a region that uses semi-colons instead of commas for the list separator. Try changing those commas to semi-colons. Opening my workbook on your computer should adjust it on its own.




This formula works in my sheet. The formula in your screenshot has the wrong syntax of criteria_range and criteria.

COUNTIFS function - Microsoft Support


Thank you!!!!! It works! :D And if I want it to find only part of a value. For example, if the colour was midnight blue and indigo blue but I only wanted to target blue to gather every blue?


Add a wildcard: