SOLVED

Sum by group excel (non-vba nor power query)

%3CLINGO-SUB%20id%3D%22lingo-sub-3070537%22%20slang%3D%22en-US%22%3ESum%20by%20group%20excel%20(non-vba%20nor%20power%20query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3070537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EPlease%2C%20I%20want%20to%20sum%20by%20group%2C%20however%20I%20have%20some%20conditions.%20For%20exemple%3A%20I%20have%20different%20fruits%20divided%20in%20groups%20(1%20to%205)%20in%20Table%201.%20In%20Table%202%2C%20I%20have%20the%20quantity%20of%20some%20fruits%20that%20are%20declared%20in%20Table%201.%20In%20table%203%2C%20I%20need%20to%20calcule%20the%20total%20number%20of%20the%20fruits%20from%20table%202%20in%20each%20group%20according%20to%20table%201.%20Also%2C%20I%20need%20it%20to%20be%20dynamic.%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341948iB628FF567B1BFE90%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%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3070537%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-3070557%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20by%20group%20excel%20(non-vba%20nor%20power%20query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3070557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1285474%22%20target%3D%22_blank%22%3E%40fhceq%3C%2FA%3E%26nbsp%3BJust%20a%20quick-and-dirty%20model%20in%20the%20attached%20sheet.%20Perhaps%20you%20can%20apply%20it%20to%20your%20real%20data.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-01-24%20at%2021.06.38.png%22%20style%3D%22width%3A%20555px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341953iFB337746814077F0%2Fimage-dimensions%2F555x228%3Fv%3Dv2%22%20width%3D%22555%22%20height%3D%22228%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-01-24%20at%2021.06.38.png%22%20alt%3D%22Screenshot%202022-01-24%20at%2021.06.38.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIt%20requires%20Excel365%20or%202021%2C%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Please, I want to sum by group, however I have some conditions.

For exemple: I have different fruits divided in groups (1 to 5) in Table 1.

In Table 2, I have the quantity of some fruits that are declared in Table 1.

In table 3, I need to calculate the total number of the fruits from table 2 in each group according to table 1.

Also, I need it to be dynamic.image.png

11 Replies

@fhceq Just a quick-and-dirty model in the attached sheet. Perhaps you can apply it to your real data.

Screenshot 2022-01-24 at 21.06.38.png

It requires Excel365 or 2021, though.

@fhceq 

As variant

image.png

with

=LET(
  groups, UNIQUE(Table1[Group]),
  qty, MMULT( --(groups=TRANSPOSE(Table1[Group]) ), XLOOKUP(Table1[Fruits],Table2[Fruit],Table2[Qty], 0) ),
  IF( {1,0}, groups, qty) )
best response confirmed by fhceq (Occasional Contributor)
Solution

@fhceq 

 

Another variation I think will work:

 

=SUM((Table1[Groups]=[@Group])*(Table1[Fruits]=TRANSPOSE(Table2[Fruit]))*TRANSPOSE(Table2[Qty]))

@fhceq 

I think the most straightforward approach is to introduce a helper column to Table 1 to bring the quantities across from Table 2.

= XLOOKUP(
    [@Fruits],
    Table2[Fruits],
    Table2[Quantity],
  0)

That leave a simple SUMIFS to obtain the result

= SUMIFS(
    Table1[Quantity],
    Table1[Groups],
    groups#)

where 'groups#' is a unique list of the distinct groups.

Naturally, I also set out to achieve the result with a single formula.  The trouble is that SUMIFS expects the 'quantities to be summed' to be a range reference and not an array.  To, instead, work cell by cell using MAP, one could have

= LET(
    grouping, Table1[Groups],
    quantity, XLOOKUP(Table1[Fruits],Table2[Fruits],Table2[Quantity],0),
    SumIfsλ,  LAMBDA(qty,defGrp, 
      LAMBDA(grp, 
        SUM(FILTER(qty,defGrp=grp)))),
    MAP(groups#, 
      SumIfsλ(quantity,grouping)
    )
  )

 

@fhceq 

=SUM(IF($A$3:$A$15=G3,MMULT(--($B$3:$B$15=TRANSPOSE($D$3:$D$10)),$E$3:$E$10)))

 

An alternative could be above formula as shown in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.

@Riny_van_Eekelen, thank you so much!
You helped me a lot with your solution.
@Sergei Baklan, thank you so much!
I had never used "LET" and "UNIQUE" functions until now, and your solution helped me to know they exist, and I could learn a bit more about them, specially because they were applied in a situation I was dealing to.
Thank you!

@fhceq 

You are welcome. I'd recommend to start from any of such function page, e.g. FILTER function (microsoft.com) and check all references

image.png

@JMB17, thank you so much!
Your solution helped me a lot!
@Quadruple_Pawn, thank you so much!
Your solution helped me a lot!