I am sifting through a dataset that has comments in a single column. I would like to identify the number of cells that contain at least one occurrence of word1 and word2. The command should tell me the number of cells that contain both words regardless of the order or placement of the words within the cell.
when i try to use the =COUNTIFS(columnrow1:columnrow2, "*word1*", columnrow1:columnrow2, "*word2*") I receive a number that indicates all cells containing word1 followed by word2 (other words can be between them). however, this does not indicate cells that contain word2 before word1.
I then try to use the command =COUNTIFS(columnrow1:columnrow2, "*word1*", columnrow1:columnrow2, "*word2*") + COUNTIFS(columnrow1:columnrow2, "*word2*", columnrow1:columnrow2, "*word1*").
This formula returns cells that contain both word1 and word2 in either order, however there is some double counting of cells that contain the word1 followed by word2 followed by word1 (or other similar combinations.)
Please tell me the command that allows me to search a single column for cells containing word1 AND word2
Bookmarks