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:
Where each table is in a sheet named as the tablename.
Or, you can also use this other array formula:
Note: To enter a formula as array formula, you need to hold down Ctrl+Shift and the press Enter after typing in the formula.