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

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.


As variant



  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)



Another variation I think will work:




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


That leave a simple SUMIFS to obtain the result


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, 





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!


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


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