Forum Discussion
Find the sum of values found with offset function
Hi!
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
- I didn´t even know these existed. Thank you very much!
As variant
by
=LET( 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 ) ) )- Oh thats interesting too, thanks!
=SUMIF($B$3:$B$7,E3,$C$3:$C$7)
Is this what you are looking for (or do you only want a solution with the offset formula) ?
- Yes, thank you!
