Lets say I have an excel sheet. Cell A1 has the text "car". I need to count all the cells with the word "car" as a part of centence with COUNT.IF. For example if another cell has the pharace "Car is driving fast" the COUNT.IF can pick it up based on the value in A1. Now my COUNT.IF -function has the "* car *" manually written. If I change the value in A1 I have to manually change the code. Is there a way or another function that would do the tric?

See above. My current function is written like this

=IF(COUNT.IF($C$20:$Q$21;"* car *") * 1=0;"";COUNT.IF($C$20:$Q$21;" * ("*car *")*1)

But id like to pick the word car from another cell and I cant copy the function efficiently since I have to manually fill the words I want to search from phraces. I just need the amount of cell with a word given by A1 as part of a sentence.

CodePudding user response：

You can use the CONCATENATE function to combine the value in cell A1 with your search string. The CONCATENATE function allows you to combine text from multiple cells into a single cell.

For example, you could use the following formula to combine the value in A1 with your search string:

=CONCATENATE("* ", A1, " *")

This formula will return the text "* car *" if A1 contains the text "car".

You can then use this formula as the search string in your COUNT.IF function, like this:

=IF(COUNT.IF($C$20:$Q$21, CONCATENATE("* ", A1, " "))=0,"",COUNT.IF($C$20:$Q$21, CONCATENATE(" ", A1, " *")))

This will count the number of cells in the range $C$20:$Q$21 that contain the word in cell A1 as part of a sentence. If there are no cells that contain the word, the formula will return an empty string.

I hope this helps!

CodePudding user response：

I think you are looking for something along these lines:

=SUM(--ISNUMBER(FIND(" "&A1&" "," "&C20:Q21&" ")))

Swap SUM() with SUMPRODUCT() if you are using excel prior to ms365/2021

Do note that the wap you have set this up is it not checking for the word 'car' with leading/trailing punctuation marks.