SOLVED

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

Copper 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 (Copper 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!
@OliverScheurich, thank you so much!
Your solution helped me a lot!
1 best response

Accepted Solutions
best response confirmed by fhceq (Copper Contributor)
Solution

@fhceq 

 

Another variation I think will work:

 

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

View solution in original post