2

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?

Excellll
  • 12,717
anony
  • 35

1 Answers1

1

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.

Community
  • 1
Jerry
  • 4,936
  • indeed, that other question has some similarities, don't know how I could not find it before making a new one >.< in either case, this answer helped me greatly. Thank you! – anony Mar 28 '14 at 15:52
  • @anony It's okay, no need to worry. After all, it was on a sister site and it's not always easy to find the question that addresses one's problem :) – Jerry Mar 28 '14 at 15:54