SOLVED

Find the sum of values found with offset function

%3CLINGO-SUB%20id%3D%22lingo-sub-3006720%22%20slang%3D%22en-US%22%3EFind%20the%20sum%20of%20values%20found%20with%20offset%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3006720%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cant%20figure%20out%20a%20solution%20for%20this%20one%20on%20my%20own.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20table%20on%20the%20let%2C%20there%20are%20fruits%20that%20appear%20multiple%20times.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20get%20the%20sum%20of%20the%20amounts%20to%20the%20right.%20So%20what%20I%20need%20to%20do%20is%20to%20find%20all%20the%20cells%20with%20%22Apple%22%20from%20the%20table%20and%20then%20offset%20one%20cell%20to%20the%20right%20and%20then%20sum%20up%20all%20the%20values.%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20pretty%20sure%20this%20can%20be%20done%20with%20PivotTables%2C%20but%20does%20anyone%20know%20a%20way%20this%20can%20be%20done%20within%20a%20single%20cell%2C%20without%20creating%20a%20new%20table%20or%20so%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20manage%20to%20pull%20it%20off%20with%20finding%20a%20single%20value%20with%20offset%20but%20as%20soon%20as%20multiple%20values%20and%20the%20sum%20function%20come%20in%20I%20get%20lost.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22UsefulDragon_0-1637868366747.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329496iA4FB181C10CF2125%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22UsefulDragon_0-1637868366747.png%22%20alt%3D%22UsefulDragon_0-1637868366747.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20attached%20a%20file%20if%20its%20useful%20in%20anyway%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3006720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3006740%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20the%20sum%20of%20values%20found%20with%20offset%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3006740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1229713%22%20target%3D%22_blank%22%3E%40UsefulDragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF(%24B%243%3A%24B%247%2CE3%2C%24C%243%3A%24C%247)%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%20(or%20do%20you%20only%20want%20a%20solution%20with%20the%20offset%20formula)%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3006849%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20the%20sum%20of%20values%20found%20with%20offset%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3006849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1229713%22%20target%3D%22_blank%22%3E%40UsefulDragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20634px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329513iEFF83F917B66E186%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eby%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20fruit%2C%20%20%20%24B%243%3A%24B%247%2C%0A%20%20amount%2C%20%20%24C%243%3A%24C%247%2C%0A%20%20u%2C%20%20%20%20%20%20%20SORT(%20UNIQUE(%20fruit%20)%20)%2C%0A%20%20isFruit%2C%20--(%20u%20%3D%20TRANSPOSE(%20fruit%20)%20)%2C%0A%20%20CHOOSE(%20%7B1%2C2%7D%2C%20u%2C%20MMULT(%20isFruit%2C%20amount%20)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

UsefulDragon_0-1637868366747.png

I attached a file if its useful in anyway

 

Thank you

6 Replies

@UsefulDragon 

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

@UsefulDragon 

As variant

image.png

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

@UsefulDragon 

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

 

JulianoPetrukio_1-1637882255762.png

 

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