Excel Formula for Defining Range for COUNTIF value in Dynamic Data

Question

The scenario...

1. The Excel file is generated from dynamically loaded data into an Excel table.
2. One column has a value that is associated with a group of records.
3. The group of records can range from a single record to dozens.
4. A second column has a variety of meta-data values.
5. Next column contains a formula to identify if a given meta-data value exists in the rows of the group. EXAMPLE: [NON-HUMANS] =IF(COUNTIF(RANGE,"<>HUMAN")>0,"YES","NO")

NOTE - The group value correlates to a UUID of which there are hundreds, for the sake of simplifying the sample data, I have used ALPHA, BRAVO, CHARLIE, ETC.

The issue - as the number of group records varies, I need to define the range in the formula based on the GROUP column value.

SAMPLE DATA SET

GROUP,MEMBERS,SPECIES
ALPHA,Scooby,DOG
ALPHA,Shaggy,HUMAN
ALPHA,Velma,HUMAN
ALPHA,Daphne,HUMAN
ALPHA,Fred,HUMAN
BETA,Alvin,CHIPMUNK
BETA,Simon,CHIPMUNK
BETA,Theodore,CHIPMUNK
CHARLIE,Superman,HUMAN
CHARLIE,Batman,HUMAN
CHARLIE,Robin,HUMAN
CHARLIE,Wonder Woman,HUMAN
CHARLIE,Aquaman,HUMAN
CHARLIE,Wonder Dog,DOG
DELTA,Spider-Man,HUMAN
DELTA,Ice-Man,HUMAN
DELTA,Firestar,HUMAN

Answer 1

If you have Excel O365 then you can utilize XMATCH(). Try-

=IF(A2<>A1,SUM(--((INDEX($C$2:$C$18,XMATCH(A2,$A$2:$A$18,0,1)):INDEX($C$2:$C$18,XMATCH(A2,$A$2:$A$18,0,-1)))<>"HUMAN")),"")