What is the Excel formula to countif text partially makes up a cell

0 votes
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 in Database by Edureka
• 13,640 points
35 views

1 answer to this question.

0 votes

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:

image

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

answered Mar 30 by gaurav
• 13,460 points

Related Questions In Database

0 votes
1 answer

I have to build a school website How can i connect databases to the instance (ec2) and what is the best AMI for my webserver INAWS ???

Hi@Trinu, I don't think your database is depends ...READ MORE

answered May 4, 2020 in Database by MD
• 95,360 points
288 views
0 votes
1 answer

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

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

answered Mar 30 in Database by gaurav
• 13,460 points
28 views
0 votes
1 answer

Excel formula to remove space between words in a cell

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

answered Mar 30 in Database by gaurav
• 13,460 points
36 views
0 votes
1 answer

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

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

answered Apr 4 in Database by Edureka
• 13,640 points
86 views
0 votes
1 answer
0 votes
1 answer

Remove special characters from the specified string in excel

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

answered Mar 15 in Database by gaurav
• 13,460 points
89 views
0 votes
1 answer

Remove duplicates within a row

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

answered Mar 15 in Database by gaurav
• 13,460 points
43 views
0 votes
1 answer
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15 in Database by gaurav
• 13,460 points
102 views
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25 in Database by gaurav
• 13,460 points
72 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP