SOLVED

Find the sum of values found with offset function

Occasional Contributor

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

Re: Find the sum of values found with offset function

=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) ?

Re: Find the sum of values found with offset function

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 ) ) )``````

best response confirmed by UsefulDragon (Occasional Contributor)
Solution

Re: Find the sum of values found with offset function

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

Yes, thank you!

Re: Find the sum of values found with offset function

Oh thats interesting too, thanks!

Re: Find the sum of values found with offset function

I didn´t even know these existed. Thank you very much!