Apr 10 2024 08:21 AM
Hello!
Need help with counting how many time three words match in the same line in three different columns.
Thank you!
Apr 10 2024 08:33 AM
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?
Apr 10 2024 10:06 AM - edited Apr 10 2024 10:10 AM
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))
)
)
)
)
Apr 10 2024 10:18 AM
Apr 10 2024 10:20 AM
Apr 10 2024 10:31 AM
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)
Apr 10 2024 10:46 AM
Apr 10 2024 10:53 AM
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)
Apr 10 2024 12:09 PM
Apr 10 2024 12:58 PM
Apr 10 2024 01:28 PM
Apr 10 2024 01:49 PM - edited Apr 11 2024 04:33 AM
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.
Apr 11 2024 03:04 AM
=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
Apr 11 2024 12:58 PM
Apr 11 2024 01:04 PM
Apr 11 2024 01:24 PM
Apr 11 2024 01:35 PM