Forum Discussion
Sum by group
- Aug 21, 2023
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.
My preference when aggregating data by group is to create a new table that lists the unique combinations of attributes that define the group.
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.
- PeterBartholomew1Aug 20, 2023Silver Contributor
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).
- AmGelainAug 21, 2023Copper Contributor
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.
- HansVogelaarAug 21, 2023MVP
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.