Consolidate Data and Sum Value

JMJ

New Member
Joined
Feb 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
Thanks for any help you can provide!
 

Attachments

  • example.png
    example.png
    11.6 KB · Views: 9

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,142
Office Version
  1. 365
Platform
  1. Windows
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.

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top