SOLVED

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

Occasional Contributor

# 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

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

@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.

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

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) )``````
best response confirmed by fhceq (Occasional Contributor)
Solution

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

Another variation I think will work:

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

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

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)
)
)``````

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

=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.

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

@Riny_van_Eekelen, thank you so much!
You helped me a lot with your solution.

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

@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!

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

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

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

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

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

@Peter Bartholomew, thank you so much!