Forum Discussion
Sum by group
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), "") ) ))