How to sum multiple columns using GROUPBY() function?
I have the following sample dataset.
| Item | Value1 | Value2 |
|---|---|---|
| a | 5 | 15 |
| b | 10 | 20 |
| c | 5 | 15 |
| d | 10 | 20 |
| a | 5 | 15 |
| b | 10 | 20 |
| c | 5 | 15 |
| d | 10 | 20 |
| a | 5 | 15 |
| b | 10 | 20 |
| c | 5 | 15 |
| d | 10 | 20 |
I want to sum column wise values based on unique items. So, my output will look like-
| a | 15 | 45 |
|---|---|---|
| b | 30 | 60 |
| c | 15 | 45 |
| d | 30 | 60 |
Currently I can achieve desired result by the following formula-
=LET(x,UNIQUE(B4:B15),HSTACK(x,SUMIFS(C4:C15,B4:B15,x),SUMIFS(D4:D15,B4:B15,x)))
Can I get same result by GROUPBY() function? =GROUPBY(B4:B15,C4:C15,SUM) this formula sum a single column.
Screenshot for better understanding.
Solution
I got the trick of GROUPBY() function. It supports to input multiple column range and SUM values for individuals columns by grouping items. The following formula solved my issue.
=GROUPBY(B3:B15,C3:D15,SUM,3)
- How to change Chart xAxes font color from black to red via vba code?
- using VBA in Excel I would like to replace only blank cells with a formula without selecting all the time
- If value matches x, enter new value into another table using row and column matches
- Create a smaller dynamic list in Excel from data in a larger list using multiple criteria
- How to loop through dimetial array using VBA in excel?
- Correct calculation of r square (R^2) using visual basic WorksheetFunction.LinEst function
- Copy column data to new column, if data exists, use column to the right
- Best practice for checking if an cell entry will generate an error
- Spill formula to sumifs by row & col for only relevant months
- How to keep value of merged cells in each cell?
- Python - Automatically adjust width of an excel file's columns
- How to Define the row after Lastrow in VBA
- How to convert big numbers between HEX and DEC in Excel without using VBA?
- Preventing ClearContents on Combination of Merged and Unmerged Cells
- drop_duplicates not working in pandas?
- Word VBA: How to reference a Named Range/Cell from Excel so it returns the row number
- Convert excel to pdf with VBScript
- Forbidden custom function name in Excel
- copy file to a dynamic folder using VBA
- VB: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)
- Pie chart with three fields in Ms Excel
- Excel: Put formula in next row after header text, like in Google Sheets
- Copy cells based on matching index numbers on excel?
- Adding holiday parameter into NetworkDays function
- Split a cell into 2 rows in Excel
- How to Combine Text Based on Multiple Criteria in Excel
- The VBA code slows down the worksheet needs to be faster
- Dealing with struck-through and non-struck-through characters using Python on Linux and/or MacOS
- Renaming worksheets by VBA
- Add Table to Excel File with R, without overwriting excisiting content