Forum Discussion
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:...
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.
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).
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.
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.
- Thank you very much for helping me. I was having trouble with this formula.
I think that will match the OP's expectations somewhat better!
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), "") ) ))