# Consolidate Data and Sum Value

#### JMJ

##### New Member
Hi, I would like to take the data in the yellow table and make it look like the green table. I tried using Consolidate, but I lose the Rep column and the Unit Price column gets summed. Product, Rep, and Unit Price could be considered a unique identifier. For all the rows in which they are the same, I only need them listed once and would like a their Quantity and Total Cost to be summed as shown in the green table. I don't work with excel much and haven't found an answer to this specific question online. I am using Excel 365.

#### Attachments

• example.png
11.6 KB · Views: 9

#### lrobbo314

##### Well-known Member
No problem. Having fun with the dynamic array formulas.

Here is a version that does the totals for you as well.

Book One.xlsx
ABCDEFGHIJK
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill1501150Product 1Jill1505750
3Product 1Jill1501150Product 2Jill2002400
4Product 1Jill1503450Product 2Scott1005500
5Product 2Jill2002400Product 1Bill503150
6Product 2Scott1003300Product 3Bill257175
7Product 2Scott1001100 Totals: 525221,975
8Product 2Scott1001100
9Product 1Bill503150
10Product 3Bill25125
11Product 3Bill25375
12Product 3Bill25375
Sheet4
Cell Formulas
RangeFormula
G2:K7G2=LET(ft,UNIQUE(TBL[[Product]:[Unit Price]]),pc,TBL[Product],rc,TBL[Rep],qc,TBL[Quantity],tc,TBL[Total Cost],up,INDEX(ft,,1),ur,INDEX(ft,,2),tq,SUMIFS(qc,pc,up,rc,ur),tot,SUMIFS(tc,pc,up,rc,ur),IFERROR(INDEX(CHOOSE({1,1,1,2,3},ft,tq,tot),SEQUENCE(ROWS(ft)+1),{1,2,3,4,5}),CHOOSE({1,2,3,4,5}," ","Totals: ",SUM(ft),SUM(tq),SUM(tot))))
Dynamic array formulas.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Replies
6
Views
135
Replies
17
Views
256
Replies
5
Views
263
Replies
4
Views
126
Replies
4
Views
80

1,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back