How to group a column based on another column. Then write it into another column

Question

How to group the animal name column based on the category column, and put it on the corresponding column (yellow area). For example in the figure, find the animal name (column A) with herbivore (column B) category, and put it in column C (yellow area).

If each item in herbivore has a 30 score, write the total item times 30 to the total (cell labelled X1)

Answer 1

Please read the description of the FILTER() function and apply it to your data:

=FILTER($A$2:$A$29,$B2:$B29=C$2)

Now read the description of the COUNTIF() function and put in the thirtieth row

=COUNTIF($B$2:$B$29,C$2)*30

Answer 2

Since you tagged Microsoft Excel 2016 you need the bypass FILTER not being available. We could achieve this using INDEX and AGGREGATE:

Use this in C3 use the formula below and drag down/right: =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$3:$B$29)/($B$3:$B$29=C$2),ROW(A1))),"")

We use AGGREGATE with the row numbers of column B from where the range of category starts till where it ends: B3:B29. We use $ to lock the position of the range rows and columns.

The array of row numbers is divided by the Boolean of the cell's value in the array being equal to the value of C2 resulting in TRUE or FALSE row wise.

Because we use a / operator to the array, the Boolean is transformed to it's numerical equivalent: TRUE = 1, FALSE = 0.

So the array of row numbers is divided by an array of 1's and 0's. We take advantage of the (row) number divided by 0 resulting in an error (#DIV/0!); AGGREGATE's second argument: 6 means ignore error values. So that means AGGREGATE will only use the array of row numbers meeting the condition, but the first argument has to make it return the nth position of that array. We can use 14 SMALL, or 15 LARGE to return the nth position of the aggregated array. I used ROW(A1) (equals 1) for the first - which will become A2 (equals 2), etc. - when dragged down.

IFERROR is used if the nth row number exceeds the number of rows of the aggregated array, which would return an error for the index.

For the INDEX I used whole column reference, so we don't need to calculate the row minus the start of the array +1 making it unnecessarily complicated.

For the Total you could use SUMPRODUCT in C30 (drag to the right): =SUMPRODUCT(N($B$3:$B$29=C$2))*30

The same Boolean logic is used as in the aggregate function and N() is used to transform the TRUE and FALSE to 1 and 0. SUMPRODUCT sums th 1's and 0`s and the result is multiplied by 30.

If you have a more recent Excel (2021, Office 365) you could spill the totals in one go using: =MMULT(TOROW(N(B3:B29<>"")*30),N(B3:B29=C2:E2)).

more info on MMULT here