Sum by group

[AmGelain]

Hello,

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.

Grateful.

[#2]

@AmGelain 

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

[#3]

@AmGelain 

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

[#4]

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.

[#5]

@AmGelain 

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.

= HSTACK(
    product,
    MAP(product, LAMBDA(currentInstance,
      LET(
        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).

[#6]

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

[#7]

@AmGelain 

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 @HansVogelaar could offer something closer to you needs.

= MAP(supplier, document, LAMBDA(currentSupplier,currentDocument,
      LET(
        firstOccurence, XLOOKUP(1, (supplier = currentSupplier) * (document = currentDocument), supplier,,,1),
        showTotal?,     ISREF(currentSupplier firstOccurence),
        IF(showTotal?,  SUMIFS(UnitValue, supplier, currentSupplier, document, currentDocument), "")
      )
  ))

[#8 · Accepted solution]

@AmGelain 

A "traditional" formula for F5 could be

=IF(COUNTIFS(B$4:B5,B5,$C$4:C5,C5)=1,SUMIFS(E$4:E$1000,B$4:B$1000,B5,C$4:C$1000,C5),"")

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

[#9]

@HansVogelaar 

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

[#10]

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