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