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

[melissach]

Hello!

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

Thank you!

[#2]

@melissach 

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?

[#3]

@melissach 

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

[#4]

@SergeiBaklan 

Hello!

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

Thanks! 🙂

[#5]

@Patrick2788 

Hello,

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

Thanks! 🙂

[#6]

@melissach 

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)

[#7]

Doesn't work on my example...

[#8]

@melissach 

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)

[#9]

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

[#10]

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

[#11]

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

[#12]

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.

[#13]

@melissach 

=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

[#14]

Thank you!!!!! It works! 😄 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?

[#15]

@melissach 

Add a wildcard:

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

[#16]

Thank you guys so much!!!

[#17]

You're welcome!