Count number of occurrences in Google Sheets, with multiple criteria and not considering duplicates in the row

Question

I'm looking to count the number of rows that contain a certain text (specified in the adjacent cell), from a specific range, on the basis that the criteria have been met.

I currently have this formula nearly completed, but it is counting duplicate occurrences in the same row more than once - I would like to ignore any duplicates and only count them once per row.

The current formula that I have got is as follows: =countif(flatten(filter('Data Input'!$K$2:$Z$15000,'Data Input'!$J$2:$J$15000=$B$2)),K883)

Some notes:

• K883 is the cell of reference, which I'm trying to match in the K:Z columns.
• $B$2 is the cell that needs to exist in column J of the Data Input tab, for it to be eligible for counting.
• If K883 is the word "The" and in columns K:Z of the Data Input tab says "The" twice in the same row (assuming the $b$2 condition is met), I want this to only be counted once. However, the current formula has it shown twice.

Hope I'm not too far off. Your help is very much appreciated!

Answer 1

Try flattening the data row by row with a query smush, like this:

=arrayformula( count( search( " " & K883 & " ", transpose( query( transpose( filter(" " & 'Data Input'!$K$2:$Z & " ", 'Data Input'!$J$2:$J = $B$2) ), "", 9^9 ) ) ) ) )

The enclosing spaces are for avoiding matching words that contain the, such as thesaurus and Athens.

To match case, use find() instead of search().