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

Copper 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!

16 Replies

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

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?

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

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))
)
)
)
)``````

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

Hello!

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

Thanks! :)

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

Hello,

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

Thanks! :)

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

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)``

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

Doesn't work on my example...

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

Are you getting a #NAME? error?

Here's the formula without named items:

``=COUNTIFS(A2:A27,G3:G5,B2:B27,H1:M1,C2:C27,H2:M2)``

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

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

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

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

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

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

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

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.

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

=COUNTIFS(A2:A27,"blue",B2:B27,"rose",C2:C27,"easter")

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

COUNTIFS function - Microsoft Support

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

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?

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

``=COUNTIFS(A2:A27,"*blue*",B2:B27,"rose",C2:C27,"easter")``