Find the sum of values found with offset function

Occasional Contributor



I cant figure out a solution for this one on my own.


On the table on the let, there are fruits that appear multiple times.

I would like to get the sum of the amounts to the right. So what I need to do is to find all the cells with "Apple" from the table and then offset one cell to the right and then sum up all the values. 

Im pretty sure this can be done with PivotTables, but does anyone know a way this can be done within a single cell, without creating a new table or so?


I can manage to pull it off with finding a single value with offset but as soon as multiple values and the sum function come in I get lost.



I attached a file if its useful in anyway


Thank you

6 Replies



Is this what you are looking for (or do you only want a solution with the offset formula) ?


As variant



  fruit,   $B$3:$B$7,
  amount,  $C$3:$C$7,
  u,       SORT( UNIQUE( fruit ) ),
  isFruit, --( u = TRANSPOSE( fruit ) ),
  CHOOSE( {1,2}, u, MMULT( isFruit, amount ) ) )


best response confirmed by UsefulDragon (Occasional Contributor)


"There is more than one way to skin a cat"- Seba Smith




Yes, thank you!
Oh thats interesting too, thanks!
I didn´t even know these existed. Thank you very much!