Extract info based on criteria (INDEX?)

[Ziza]

I'm investigating the potential for humane slaughter of farmed fish, which typically requires stunning them (either electrically or percussively) prior to killing. For some species (marked 'y' in the table), a suitable method has already been developed. For others, there is no known method (blank cell). For a third category, there has been no research on that specific species, but it is closely related to a species with a known method, so I've marked those '?' and put the name of the related species in a separate column. I've done this for about 270 species so far.

So, in order to prioritise species for further research, I want to know the total population ('count') of the groups of species that all may be able to use the same stunning methods. Ideally, I'd be able to see the populations of the individual species (as in 'Desired table 1' in the linked sheet) but also be able to sort the groups by total count. If this isn't feasible, something like table 2 would be good, with just the names and totals for the groups.

I could probably figure out how to do this using VLOOKUP and SUMIF if I manually typed in the names of the species with known methods, but those may change so ideally I want it all to automatically update as the table changes. I tried using INDEX but couldn't figure out the ROW bit of the formula. Some help would be much appreciated.

EDIT: It seems I can't post a link to the sheet, so I've uploaded a much-simplified version in Excel.

[dflak]

You have too much cross-referencing of the data. I streamlined it.

A fish species is similar to itself, so that's one record. I may also be similar to other species as well, and I created a row of data for each species it is similar to. I did not duplicate mappings. For example, once I associated Atlantic Salmon with Sea Trout, I removed the entry that associates Sea Trout from Atlantic Salmon.

I kept the original table to look up the stun method.

So I built the table to look like the blue table on Sheet 2.

[Ziza]

Many thanks dflak