Excel How to count the number of times a given condition was meet within a cell in Excel?

Jan 19, 2012
Reaction score
I am trying to count the number of times that words from a list appear within a cell. For instance in column A, I have a Site_Number ie Site 1, Site 2, Site 3...
In column B, I have threatened species which can be found at each site i.e Grey duck, Stitchbird, Wrybill...

Column A Column B
Site 1: Grey duck, Reef Heron, Stitchbird

Site 2: Reef heron, Grey duck

I then have a threatened species list (Column F) with the threat status associated with each particular species in the next column (Column G).

Grey duck : Critical
Black stilit : Vulnerable
Stitchbird: Endangered
Reef heron : Critical

If a species has a threat status of 'Critical' it should get a score of 3. If a species threat status is 'Endangered' it then gets a score of 2. If a particular species threat status is 'Vulnerable' then it gets a score of 1.

Thus, site 1 would be given a total score of 8 (3+3+2). This is easy enough to do manually with a couple of sites but I have a dataset which has hundreds of sites and a large list of threatened species with the associated threat status for each species in the adjacent column.

Is there a formula I can use for this problem?

If I was to manually do it I would use the following formula:

=IF(COUNTIF(B2,"*Grey duck*")=1,3)+IF(COUNTIF(B2,"*Reef heron*")=1,3)+IF(COUNTIF(B2,"*stitchbird*")=1,2)

To work out the total score for site 1 (ie. total score is 8). B2 refers to the cell which contains the threatened species at site 1.

However what I would like is to put a range (in this case the entire list of possible species) as a wild card. Is this possible though?

Although each site has up to about 10 threatened species in it's threatened species cell these threatened species names come from a list which has over 4000 species listed on it with the threat status of each of the species in the next column.
Feb 21, 2018
Reaction score
I have just seen your query...and noticed that since year 2012 it remains un-attended.
If you still need help, please let me know I will help you out.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question