using index match with sum if

0
votes

I need to link up a sumif() with an index match (i'm guessing here) but don't really know where to start.

Basically i a table with different classes of pets, their species and quantity. there are 3 stores. I need an output where i can get the quantity of each species from each store dynamically.

data table:

"A1"                Pet Stores      
Species  Class      a   b   c
cat      Fluffy1    1   0   0
cat      Fluffy2    3   0   0
cat      Fluffy3    5   7   1
cat      Fluffy4    6   0   7
dog      Barky1     7   6   9
dog      Barky2     1   3   9
dog      Barky3     0   2   8
dog      Barky4     0   2   3
fish     Swimmy1    0   0   0
fish     Swimmy2    1   3   0
fish     Swimmy3    0   2   3
fish     Swimmy4    0   0   0

Output:

Pet Store   a   <--change this
cat         15  <--output
dog         8   <--output
fish        1   <--output

right now my formula for "cat" is =SUMIF($A$3:$A$14,A17,$C$3:$C$14). however, it only looks down the 1 column that i've set. how do i change it such that it searches for the "Pet Store" and returns sum of the respective column?

2
excelexcel-formula

2 Answers

3
votes

How about this:

Formula in cell H3 copied down is

=SUMIF($A$2:$A$13,G3,INDEX($C$2:$E$13,,MATCH(H$2,$C$1:$E$1,0)))

enter image description here

0
votes

Slightly shorter that @teylyn's version:

=SUMIF(A$2:A$13,A16,OFFSET(C$2:C$13,,CODE(B$15)-97))

but less versatile as it relies on the shop names being coded (which however is as in the example and makes sense for column label purposes):

SO44381087 first example

However my preference would be for a PivotTable:

SO44381087 second example