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

Copper Contributor

Hello!

 

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

 

Thank you!

16 Replies

@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?

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

 

 

Patrick2788_0-1712768993356.png

 

 

@Sergei Baklan 

 

Hello!

 

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

 

Thanks! :)

@Patrick2788 

Hello,

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

Thanks! :)

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

 

Doesn't work on my example...

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

 

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. 

melissach_0-1712780882169.png

 

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.

@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

countifs.png

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?

@melissach 

Add a wildcard:

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