Nov 25 2021 11:41 AM
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
Nov 25 2021 11:49 AM
=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) ?
Nov 25 2021 12:38 PM
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 ) ) )
Nov 25 2021 03:18 PM - edited Nov 25 2021 03:39 PM
SolutionNov 26 2021 02:22 AM
Nov 26 2021 02:23 AM
Nov 25 2021 03:18 PM - edited Nov 25 2021 03:39 PM
Solution