Extract unique values and sum duplicates

ExcelNewbie2020

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
343
Office Version
  1. 365
Platform
  1. Windows
I need a single formula that will extract unique values from column A and B then sum the amount of duplicates. Please see result..

note: i dont have pivotby or groupby function

Book3
ABCDEFGHIJKLMNOPQ
1datefruitsdata1data2amountresult
201-07-24AppleCosta RicaCosta Rica901-07-24Apple18
301-07-24BananaCôte d'IvoireCôte d'Ivoire201-07-24Banana5
401-07-24CherryBruneiBrunei601-07-24Cherry13
501-07-24AppleMarshall IslandsMarshall Islands102-07-24Apple11
601-07-24BananaUnited Arab EmiratesUnited Arab Emirates302-07-24Banana5
701-07-24CherryBoliviaBolivia702-07-24Cherry18
801-07-24AppleMauritiusMauritius803-07-24Apple1
902-07-24BananaDominican RepublicDominican Republic303-07-24Banana7
1002-07-24CherryIsraelIsrael1003-07-24Cherry16
1102-07-24AppleBelizeBelize3
1202-07-24BananaArgentinaArgentina2
1302-07-24CherryUnited KingdomUnited Kingdom8
1402-07-24AppleSwitzerlandSwitzerland8
1503-07-24BananaMontenegroMontenegro3
1603-07-24CherryIrelandIreland4
1703-07-24AppleMadagascarMadagascar1
1803-07-24BananaTrinidad and TobagoTrinidad and Tobago4
1903-07-24CherrySaint LuciaSaint Lucia10
2003-07-24CherryTajikistanTajikistan2
21
22
Sheet8
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
How about

Book1
ABCDEFGHIJK
1datefruitsdata1data2amountresult
21-7-2024AppleCosta RicaCosta Rica91-7-2024Apple18
31-7-2024BananaCôte d'IvoireCôte d'Ivoire21-7-2024Banana5
41-7-2024CherryBruneiBrunei61-7-2024Cherry13
51-7-2024AppleMarshall IslandsMarshall Islands12-7-2024Banana5
61-7-2024BananaUnited Arab EmiratesUnited Arab Emirates32-7-2024Cherry18
71-7-2024CherryBoliviaBolivia72-7-2024Apple11
81-7-2024AppleMauritiusMauritius83-7-2024Banana7
92-7-2024BananaDominican RepublicDominican Republic33-7-2024Cherry16
102-7-2024CherryIsraelIsrael103-7-2024Apple1
112-7-2024AppleBelizeBelize3
122-7-2024BananaArgentinaArgentina2
132-7-2024CherryUnited KingdomUnited Kingdom8
142-7-2024AppleSwitzerlandSwitzerland8
153-7-2024BananaMontenegroMontenegro3
163-7-2024CherryIrelandIreland4
173-7-2024AppleMadagascarMadagascar1
183-7-2024BananaTrinidad and TobagoTrinidad and Tobago4
193-7-2024CherrySaint LuciaSaint Lucia10
203-7-2024CherryTajikistanTajikistan2
21
Sheet2
Cell Formulas
RangeFormula
H2:J10H2=LET(u,UNIQUE(A2:B20),HSTACK(u,DROP(REDUCE("",INDEX(u,,1)&INDEX(u,,2),LAMBDA(a,b,VSTACK(a,SUM(FILTER(E2:E20,A2:A20&B2:B20=b))))),1)))
Dynamic array formulas.
 
Upvote 0
Solution
Another option:
Excel Formula: 
=LET(a,A2:A20,b,B2:B20,e,E2:E20,u,UNIQUE(A2:B20),HSTACK(u,SUMIFS(e,a,INDEX(u,,1),b,INDEX(u,,2))))
 
Upvote 0
An alternative means with Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"date", "fruits"}, {{"Total", each List.Sum([amount]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"date", type date}})
in
    #"Changed Type"

Book3
ABCDEFGHI
1datefruitsdata1data2amountdatefruitsTotal
245474AppleCosta RicaCosta Rica97/1/2024Apple18
345474BananaCôte d'IvoireCôte d'Ivoire27/1/2024Banana5
445474CherryBruneiBrunei67/1/2024Cherry13
545474AppleMarshall IslandsMarshall Islands17/2/2024Banana5
645474BananaUnited Arab EmiratesUnited Arab Emirates37/2/2024Cherry18
745474CherryBoliviaBolivia77/2/2024Apple11
845474AppleMauritiusMauritius87/3/2024Banana7
945475BananaDominican RepublicDominican Republic37/3/2024Cherry16
1045475CherryIsraelIsrael107/3/2024Apple1
1145475AppleBelizeBelize3
1245475BananaArgentinaArgentina2
1345475CherryUnited KingdomUnited Kingdom8
1445475AppleSwitzerlandSwitzerland8
1545476BananaMontenegroMontenegro3
1645476CherryIrelandIreland4
1745476AppleMadagascarMadagascar1
1845476BananaTrinidad and TobagoTrinidad and Tobago4
1945476CherrySaint LuciaSaint Lucia10
2045476CherryTajikistanTajikistan2
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Count Unique Values Based on Unique (sort of) Criteria
Replies
4
Views
244
Fluff
Fluff
H
  • Solved
Query not pulling right info when running in VBA
2
Replies
13
Views
393
Holley
H
E
  • Solved
Have date and text in same cell, but still have cell function as date for formulae
Replies
3
Views
200
ExcelNew7
E
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
294
Skyybot
Skyybot
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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
Back
Top