Jan 24 2022 11:46 AM - edited Jan 24 2022 12:02 PM
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.
Jan 24 2022 12:09 PM
@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.
Jan 24 2022 12:21 PM
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) )
Jan 24 2022 01:06 PM - edited Jan 24 2022 01:08 PM
Solution
Another variation I think will work:
=SUM((Table1[Groups]=[@Group])*(Table1[Fruits]=TRANSPOSE(Table2[Fruit]))*TRANSPOSE(Table2[Qty]))
Jan 24 2022 01:47 PM
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)
)
)
Jan 24 2022 02:11 PM
=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.
Jan 25 2022 03:56 AM
Jan 25 2022 04:00 AM
Jan 25 2022 12:59 PM
You are welcome. I'd recommend to start from any of such function page, e.g. FILTER function (microsoft.com) and check all references
Jan 26 2022 10:51 AM
Jan 26 2022 10:56 AM
Jan 26 2022 10:57 AM
Jan 24 2022 01:06 PM - edited Jan 24 2022 01:08 PM
Solution
Another variation I think will work:
=SUM((Table1[Groups]=[@Group])*(Table1[Fruits]=TRANSPOSE(Table2[Fruit]))*TRANSPOSE(Table2[Qty]))