Forum Discussion

AmGelain's avatar
AmGelain
Copper Contributor
Aug 18, 2023
Solved

Sum by group

  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:...
Formulas and Functions
AmGelain's avatar
AmGelain
Copper Contributor
Aug 20, 2023

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.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 20, 2023

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

    • AmGelain's avatar
      AmGelain
      Copper Contributor
      Aug 21, 2023

      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.

Resources