excelexcel-formulaexcel-365
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)
- Powerpoint chart to excel relative link
- EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?
- VBA: How to Loop IF Statement through a Cell and Chart Series
- Incorporating Checkbox to lines of code rather than Cell value
- How do I remove a character from a column of data in Excel
- ODBC error when trying to connect MS Excel to SPSS file on fileserver
- Exporting MySQL Data Into Excel Broken By Comma
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- Filtering an Array (not Range) based on a SUM of CHOOSECOLS applied to it
- MID function inside COUNTIFS
- Delete entry from the spreadsheet and move the particular data field to other spreadsheet
- Loop to enter email address and attach applicable pdf
- Add rows to table produced with filter function based on additional argument
- Spill formula on % calculation
- Rread excel without formatting date
- Excel VBA Selecting the files xxx_Before.log then xxx_After.log
- How can I get TSQL from easy MS Access SQL with little to no handiwork?
- How to let user download the data in database to an excel sheet file from web application in Java/Struts?
- Epoch Unix Value formula
- Run Time Error 1004 'Unable to get the PivotFields property of the PivotTable class' with Data Model Pivot Table
- Python Pandas dataframe reading exact specified range in an excel sheet
- Getting Excel cell background themed color as hex with openpyxl
- SORT formula with empy column in data range and descending order based on another column (not the first column in the data range)
- Append excel cell to end of a link and create a hyperlink
- How to Insert Double or Single Quotes
- Pandas: Reading Excel with merged cells
- VBA create pivot with "data is added to the Data Model"
- Chart Formatting in Excel Scripts
- Merge cells and add borders
- Excel (Business) connector in power automate can't see the excel files created by other users in SharePoint