Aug 18 2023 04:56 PM
Aug 18 2023 04:56 PM
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.
Aug 20 2023 05:43 AM
Aug 20 2023 01:31 PM
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).
Aug 20 2023 06:03 PM
@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.
Aug 21 2023 01:50 AM
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, LET( firstOccurence, XLOOKUP(1, (supplier = currentSupplier) * (document = currentDocument), supplier,,,1), showTotal?, ISREF(currentSupplier firstOccurence), IF(showTotal?, SUMIFS(UnitValue, supplier, currentSupplier, document, currentDocument), "") ) ))