Essentially, I am having trouble finding the formula to perform a countif in an array for text that partially comprises other cells in the array.

As an example, when searching for text "fire" in an array of "fire", "arcade fire", "firetruck", and "water", I'd want the formula to return a value of three. Have tried using countif and match to no avail.

Code I've tried includes: MATCH("FIRE",B1:B4,1) where B1:B4 are the above strings. COUNTIF("FIRE",B1:B4). =IF(ISNUMBER(SEARCH("FIRE",B1:B4)),1,0) (this function is not ideal as it would not be counting).
Mar 28, 2022 in Database 13,517 views

## 1 answer to this question.

With Formulas, Countif Partial String/Substring Match
We can use the COUNTIF function in Excel to count cells by placing an asterisk before and after a partial string or substring.

Select a blank cell to store the counting result, write the formula =COUNTIF(A1:A16,"*Anne*") into it, and press the Enter key (A1:A16 is the range to count cells, and Anne is the partial string).

The total number of cells containing the partial string is then calculated. Take a look at this example:

Note: You can use the formula =COUNTIFS(A1:A16,"*Anne*",A1:A16,"*Green*") to count cells that include both substrings. (The range you'll be counting cells is A1:A16, and Anne and Green are both partial strings.)

• 23,260 points

## Excel TEXT formula doesn't convert 'yyyy' to a year

It appears that if you have a ...READ MORE

## Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

## What is the best library in python to deal with excel files?

XlsxWriter and Xlwings are the best, in ...READ MORE

## Excel - Split cell with a delimiter and if delimiter not found get complete cell value to another cell

Select the row or column that you ...READ MORE

## Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

## Remove duplicates within a row

To check for duplicates, choose the cells ...READ MORE

## EXCEL: How to automatically add serial number in Excel Table using formula that is immune to filtering / sorting?

That's a great question, and it's a ...READ MORE