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

Posted on

QUESTION :

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?

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.