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