Sum group of values then collapse?

[noname91]

Hi all,

Not sure if this will require a macro or not, but will go with functions first.

What I'm looking to do is calculate the amounts in column F, produce the sum in column G, and most importantly, have the sums only based on a group of same values in either column A or column B (either is fine). Then to have the groups collapsed into a single row showing the total in column G, and keeping everything else intact. I was thinking using if statements comparing the values in A or B would work, but then couldn't figure a way to handle the summing part in the manner described.

I've attached a workbook that shows the original table, what the calculation would like (for illustration purpose only) and the final outcome.

Is there a way to do this? Thanks for any input!

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[noname91]

Hi Glenn,

I have: Microsoft Excel 2016, as part of the Microsoft Office Professional Plus 2016 suite. My forum profile version says "MS-Off Ver: 2016", so hope this is consistent (unless it's showing something else lol)?

This is on Windows 10 Enterprise. Don't have either of the three most recent Excel products unfortunately!

Thanks.

[sandy666]

try Power Query (Get&Transform)

id	name	unused	unused2	unused3	count	Sum_Count
455387	apple	40354		Enter	33	473
422727	mango	39034		Enter	18	18
251700	cherry	40598		Enter	578	819

[noname91]

Haven't used PQ before, will take some learning, but thanks for pointing to it!

[sandy666]

sure, here is Power Query documentation