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!


1 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().

  • Wow, that's incredible! Tested and done the job - thank you very much! Jul 3, 2022 at 12:15
  • I actually have the data in one column already, but I separated each word to make it easier to search through (so I thought)... Assuming the text in K:Z in one column (e.g. column H), is it just a matter of removing the query, transpose, query section? Jul 3, 2022 at 12:34
  • Please ask only one question per post. Jul 3, 2022 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.