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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDE
1ProductRepUnit PriceQuantityTotal Cost
2Product 1Jill$150.00 1$150.00
3Product 1Jill$150.00 1$150.00
4Product 1Jill$150.00 3$450.00
5Product 1Jill$150.00 1$150.00
6Product 1Jill$150.00 2$300.00
7Product 2Scott$100.00 3$300.00
8Product 2Scott$100.00 1$100.00
9Product 2Scott$100.00 1$100.00
10
11ProductRepUnit PriceQuantityTotal Cost
12Product 1Jill$150.00 8$1,200.00
13Product 2Scott$100.00 5$500.00
Sheet6
Cell Formulas
RangeFormula
A12:E13A12=LET(pr,UNIQUE(B2:B9),p,UNIQUE(A2:A9),up,UNIQUE(C2:C9),q,SUMIF(A2:A9,p,D2:D9),tc,SUMIF(A2:A9,p,E2:E9),CHOOSE({1,2,3,4,5},p,pr,up,q,tc))
Dynamic array formulas.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows
This version is a bit better. I made the initial table into an actual table so when new data is added below it the formula will pick up the new values. And I tweaked the formula a bit too.

Book1
ABCDEFGHIJKL
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill$150.00 1$150.00 Product 1Jill$150.00 8$1,200.00
3Product 1Jill$150.00 1$150.00 Product 2Scott$100.00 6$600.00
4Product 1Jill$150.00 3$450.00 Product 3Bill$200.00 14$2,800.00
5Product 1Jill$150.00 1$150.00
6Product 1Jill$150.00 2$300.00
7Product 2Scott$100.00 4$300.00
8Product 2Scott$100.00 1$100.00
9Product 2Scott$100.00 1$100.00
10Product 3Bill$200.00 4$100.00
11Product 3Bill$200.00 2$100.00
12Product 3Bill$200.00 8$100.00
Sheet6
Cell Formulas
RangeFormula
H2:L4H2=LET(a,Table6[Product],b,Table6[Rep],c,Table6[Unit Price],d,Table6[Quantity],e,Table6[Total Cost],pr,UNIQUE(a),r,UNIQUE(b),up,UNIQUE(c),q,SUMIF(a,pr,d),tc,up*q,CHOOSE({1,2,3,4,5},pr,r,up,q,tc))
Dynamic array formulas.
 

JMJ

New Member
Joined
Feb 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thank you for fixing my example spreadsheet and for the formula! Now I need to adjust it and try it on my real spreadsheet which has a few additional columns and about 13,000 rows.
 

JMJ

New Member
Joined
Feb 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This version is a bit better. I made the initial table into an actual table so when new data is added below it the formula will pick up the new values. And I tweaked the formula a bit too.

Book1
ABCDEFGHIJKL
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill$150.00 1$150.00 Product 1Jill$150.00 8$1,200.00
3Product 1Jill$150.00 1$150.00 Product 2Scott$100.00 6$600.00
4Product 1Jill$150.00 3$450.00 Product 3Bill$200.00 14$2,800.00
5Product 1Jill$150.00 1$150.00
6Product 1Jill$150.00 2$300.00
7Product 2Scott$100.00 4$300.00
8Product 2Scott$100.00 1$100.00
9Product 2Scott$100.00 1$100.00
10Product 3Bill$200.00 4$100.00
11Product 3Bill$200.00 2$100.00
12Product 3Bill$200.00 8$100.00
Sheet6
Cell Formulas
RangeFormula
H2:L4H2=LET(a,Table6[Product],b,Table6[Rep],c,Table6[Unit Price],d,Table6[Quantity],e,Table6[Total Cost],pr,UNIQUE(a),r,UNIQUE(b),up,UNIQUE(c),q,SUMIF(a,pr,d),tc,up*q,CHOOSE({1,2,3,4,5},pr,r,up,q,tc))
Dynamic array formulas.
Thanks again for replying! I just realized there is one part of my scenario that I did not explain very well. The same Product can be associated with more than one Rep. Basically, the combination of Product and Rep make a unique identifier. I need to alter the UNIQUE part of the formula. I totally understand if you don't feel like helping again.


SIMStest.xlsx
ABCDEFGHIJK
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill$ 150.001$ 150.00Product 1Jill$ 150.005$ 750.00
3Product 1Jill$ 150.001$ 150.00Product 2Jill$ 200.002$ 400.00
4Product 1Jill$ 150.003$ 450.00Product 2Scott$ 100.005$ 500.00
5Product 2Jill$ 200.002$ 400.00Product 1Bill$ 50.003$ 150.00
6Product 2Scott$ 100.003$ 300.00Product 3Bill$ 25.004$ 100.00
7Product 2Scott$ 100.001$ 100.00
8Product 2Scott$ 100.001$ 100.00
9Product 1Bill$ 50.003$ 150.00
10Product 3Bill$ 25.001$ 25.00
11Product 3Bill$ 25.003$ 75.00
Sheet2 (3)
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows
Don't know if that can be done with a single cell formula like the last one, but this seems to do it.

Book1
ABCDEFGHIJK
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill1501150Product 1Jill1505750
3Product 1Jill1501150Product 2Jill2002400
4Product 1Jill1503450Product 2Scott1005500
5Product 2Jill2002400Product 1Bill503150
6Product 2Scott1003300Product 3Bill254100
7Product 2Scott1001100
8Product 2Scott1001100
9Product 1Bill503150
10Product 3Bill25125
11Product 3Bill25375
Sheet7
Cell Formulas
RangeFormula
G2:I6G2=UNIQUE(A2:C11)
J2:J6J2=SUMPRODUCT(IFERROR(MATCH(A2:A11,G2,0)*MATCH(B2:B11,H2,0),0)*D2:D11)
K2:K6K2=I2*J2
Dynamic array formulas.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can also just make a Pivot table out of it with 'Product' and 'Rep' in the 'Rows' section and 'Unit Price', 'Quantity', and 'Total Cost' in the 'Values' section, with 'Unit Price' being calculated as minimum and the other 2 being sum.

Book1
ABCDE
3ProductRepUnit Price Quantity Total Cost
4Product 1Bill503150
5Jill1505750
6Product 2Jill2002400
7Scott1005500
8Product 3Bill254100
9Grand Total25191900
Sheet8
 

JMJ

New Member
Joined
Feb 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thanks so much for the help! I appreciate it!
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows
1 cell formula.

Book One.xlsx
ABCDEFGHIJK
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill1501150Product 1Jill1505750
3Product 1Jill1501150Product 2Jill2002400
4Product 1Jill1503450Product 2Scott1005500
5Product 2Jill2002400Product 1Bill503150
6Product 2Scott1003300Product 3Bill254100
7Product 2Scott1001100
8Product 2Scott1001100
9Product 1Bill503150
10Product 3Bill25125
11Product 3Bill25375
Sheet4
Cell Formulas
RangeFormula
G2:K6G2=LET(tbl,UNIQUE(TBL[[Product]:[Unit Price]]),p,INDEX(tbl,,1),r,INDEX(tbl,,2),qc,TBL[Quantity],tc,TBL[Total Cost],pc,TBL[Product],rc,TBL[Rep],q,SUMIFS(qc,pc,p,rc,r),up,INDEX(tbl,,3),t,up*q,CHOOSE({1,2,3,4,5},p,r,up,q,t))
Dynamic array formulas.
 
Solution

JMJ

New Member
Joined
Feb 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
1 cell formula.

Book One.xlsx
ABCDEFGHIJK
1ProductRepUnit PriceQuantityTotal CostProductRepUnit PriceQuantityTotal Cost
2Product 1Jill1501150Product 1Jill1505750
3Product 1Jill1501150Product 2Jill2002400
4Product 1Jill1503450Product 2Scott1005500
5Product 2Jill2002400Product 1Bill503150
6Product 2Scott1003300Product 3Bill254100
7Product 2Scott1001100
8Product 2Scott1001100
9Product 1Bill503150
10Product 3Bill25125
11Product 3Bill25375
Sheet4
Cell Formulas
RangeFormula
G2:K6G2=LET(tbl,UNIQUE(TBL[[Product]:[Unit Price]]),p,INDEX(tbl,,1),r,INDEX(tbl,,2),qc,TBL[Quantity],tc,TBL[Total Cost],pc,TBL[Product],rc,TBL[Rep],q,SUMIFS(qc,pc,p,rc,r),up,INDEX(tbl,,3),t,up*q,CHOOSE({1,2,3,4,5},p,r,up,q,t))
Dynamic array formulas.
This is even better, thanks!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,031
Members
416,892
Latest member
Bensch

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