Sum by group

Copper Contributor




I would like to group the unit values ​​of the products regardless of the order of the document:


When changing the price order, calculate this way, as the formula is as follows: =IF(C3=C2;"";SUMIF(C:C;C3;E:E))


In this formula, the DOCUMENT column is considered to perform the sum, but I need to use the DOCUMENT and SUPPLIER column, to then consider the sumif. I ask for a formula suggestion to solve my problem.




9 Replies


I'd create a pivot table based on the data.


My preference when aggregating data by group is to create a new table that lists the unique combinations of attributes that define the group.


Thanks for your return. However, I need to retain the structure of the first image attached in my question, this is a block of a much larger spreadsheet. And according to the second image, even if the supplier and the documents are in disarray, I need it according to the first image. Thank you for your concern to help me.


That is just a bit more complicated.  Would you want to display the total against the first member of each group or the last?


A start towards a solution was to be found to the right of the previous spreadsheet.

    MAP(product, LAMBDA(currentInstance,
        firstOccurence, XLOOKUP(currentInstance, product, product,,,1),
        showTotal?,     ISREF(currentInstance firstOccurence),
        IF(showTotal?,  SUMIFS(UnitValue, product, currentInstance), "")

First or last depends upon the final parameter of the XLOOKUP but, as it stands, the match is made on the basis of a single attribute (product) rather than two (supplier and document).

@peter Bartolomeu I believe that the problem is in =(IFC3=C2;""; which needs to consider the document and the supplier (even if out of order) to then apply the second part of the formula SUMIF(C:C;C3;E:E) ), to then maintain the structure of the first image of the question.


I have put together a solution to what I understand to be your problem.  All of the methods it uses are specific to Excel 365 and I am not sure you are a 365 user.  Also I suspect my approach to problem solving in Excel will be alien as far as you are concerned.  If so, it may be that @Hans Vogelaar could offer something closer to you needs.


= MAP(supplier, document, LAMBDA(currentSupplier,currentDocument,
        firstOccurence, XLOOKUP(1, (supplier = currentSupplier) * (document = currentDocument), supplier,,,1),
        showTotal?,     ISREF(currentSupplier firstOccurence),
        IF(showTotal?,  SUMIFS(UnitValue, supplier, currentSupplier, document, currentDocument), "")
best response confirmed by AmGelain (Copper Contributor)


A "traditional" formula for F5 could be




Fill down. See the attached workbook, using @Peter Bartholomew's example.

@Hans Vogelaar 

:cool:I think that will match the OP's expectations somewhat better!

Thank you very much for helping me. I was having trouble with this formula.