1

Having the following sheet “Animals”, how do I get an inventory of number of Birds and Mammals?:

|   | A       | B   |  
| - | ------- | --- |  
| 1 | Animal  | Qty |  
| 2 | Chicken | 5   |  
| 3 | Duck    | 2   |  
| 4 | Pig     | 7   |  
| 5 | Horse   | 4   |

considering the sheet “Classes” matches each animal to a class:

|   | A       | B      |  
| - | ------- | ------ |  
| 1 | Animal  | Class  |  
| 2 | Chicken | Bird   |  
| 3 | Duck    | Bird   |  
| 4 | Pig     | Mammal |  
| 5 | Horse   | Mammal |

The sheet “Inventory” would be like this.

What formula I can fill each cell in column B with, that counts the number of animals by class?

|   | A      |  B    |  
| - | ------ | ---   |  
|   | Class  | Qty   |  
| 1 | Bird   | =(7)  |  
| 2 | Mammal | =(11) |

I thought about SUMIF like in =SUMIF(Animals!B:B, condition), but I cannot seemingly combine VLOOKUP in condition, like in "=VLOOKUP(Animals!A2, Classes!A:B, 2, FALSE)". I am not one bit familiar with array formulas, if that is what one would need to do.

I checked other similar questions, in Stack Overflow and others, but the ones I found, seem to have a condition that checks against specific values instead of a formula.

Community
  • 1
  • 1
elmimmo
  • 442
  • 2
  • 11

1 Answers1

2

Try the following:

=ArrayFormula(SUMIFS($B$2:$B$5,
              VLOOKUP($A$2:$A$5,
                      $A$8:$B$11,
                      2,
                      FALSE),
              A14))

The vloopup returns an array {"bird","bird","mammal","mammal"} that SUMIFS() checks agains to be the class in A14.

In this case I laid out the data like this for convenience:

  • The first table starts with aniaml in A1
  • The second table starts with animal in A7
  • The last table starts with class in A13.
Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37
  • Awesome. I rewrote your formula to use the sheets in my example like this `=ArrayFormula(SUMIFS(Animals!$B:$B,VLOOKUP(Animals!$A:$A,Classes!$A:$B,2,FALSE),$A2))` for cell B2 in “Inventory”, and then copy-pasted to B3 letting Google Spreadsheets do the increment. Extra thanks for the explanation of how it works. – elmimmo Nov 13 '15 at 12:25