Excel - sum values based on a column that match another column in another table

I've been working my head around this, but haven't figured it out yet, and I'm sure it's dead simple.

Suppose the following example:
There are two tables, one containing animals, and the other one containing animal count for each animal. Additionally, each animal belongs to a type, mammals, birds, bugs, reptiles, etc.

Table1 - Animals

Animal Type
parrot birds
spider bugs
zebra mammals
seagull birds
elephant mammals
fly bugs
snake reptiles

Table2 - Animal Count

Animal Count
zebra 4
snake 1
spider 3
elephant 0
parrot 2
seagull 3
fly 5

The idea would be to generate a table with totals of animals for each type of animal. Each cell on column 'Total' from Table3 would look up its corresponding 'Type' on Table1 and sum all rows in 'Count' from Table2 that match an animal from that 'Type'.

Table3 - Result

 Type Total
mammals 4 (zebra + elephant)
birds 5 (parrot + seagull)
bugs 8 (fly + spider)
reptiles 1 (snake)

Is there a way to perform this matching?

1 Answer

A similar question had been asked yesterday. If you already have table3 with the different types of animals ready, you can use this array formula to retrieve the count:

=SUM(IF(COUNTIFS('table1'!A$1:A$7,'table2'!A$1:A$7,'table1'!B$1:B$7,A1)>0,'table2'!B$1:B$7,0))

Where each table is in a sheet named as the tablename.

Or, you can also use this other array formula:

=SUM(SUMIF('table2'!A$1:A$7,IF('table1'!B$1:B$7=A1,'table1'!A$1:A$7),E$1:E$7))

Note: To enter a formula as array formula, you need to hold down Ctrl+Shift and the press Enter after typing in the formula.