Forum Discussion
Sum by group excel (non-vba nor power query)
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.
11 Replies
=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.
- OliverScheurich, thank you so much!
Your solution helped me a lot!
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) ) )- PeterBartholomew1, thank you so much!
As variant
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) )- SergeiBaklan, 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 (microsoft.com) and check all references
fhceq Just a quick-and-dirty model in the attached sheet. Perhaps you can apply it to your real data.
It requires Excel365 or 2021, though.
- Riny_van_Eekelen, thank you so much!
You helped me a lot with your solution.
