How do you count if a cell contains partial text in Excel

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, 2022 in Database by Edureka
• 13,690 points
17,751 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, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

how do I calculate discount with if function in excel

Notes about the release; Frameworks to aim ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
793 views
0 votes
1 answer

How do I display a ratio in Excel in the format A:B?

Try this formula: =SUBSTITUTE(TEXT(A1/B1,"?/?"),"/",":") Result: A B ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
1,033 views
0 votes
0 answers

How to split a string of text in excel based on a given word?

I have a list of combinations of ...READ MORE

Apr 4, 2022 in Database by gaurav
• 23,260 points
544 views
0 votes
1 answer

EXCEL How to check if Column A contains specific value and Column B contains 2 specific values?

If you're looking for the string CHECK in cells ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
708 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, 2022 in Database by gaurav
• 23,260 points
1,765 views
0 votes
1 answer

Remove duplicates within a row

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

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
718 views
0 votes
1 answer
0 votes
1 answer

How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
21,469 views
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
11,622 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP