Extracting name once & amount totaled how can I do it with Formulas?

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
431
Office Version
  1. 365
Platform
  1. Windows
I currently I have users with amount and they show up like this on my excel ledger:

James Smith 100
James Smith 50
Bob James 45
Jim Kraig 30
Tom Jones 75
Tom Jones 10
Billy Jack 36
Billy Jack 75
Billy Jack 75


Now I do a Sort to group them all together by name & numbers.
But I want to break them out with an =INDEXor array etc to be like this:

James Smith = 150
Bob James = 45
Jim Kraig = 30
Tom Jones = 85
Billy Jack = 186

Can some one Helpppppppppppp Thanks so much
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
1) Are the names and amounts inside the same cell?
2) Which version of XL are you using?
3) Have you considered Pivot tables?
 
Upvote 0
Can do a groupby either in Excel or Power Query as well as a Pivot table as mentioned earlier. Knowing the XL version would let us know which of these would work for you.
 
Upvote 0
I cannot tell how your data looks.

Book2
A
1James Smith 100
2James Smith 50
3Bob James 45
4Jim Kraig 30
5Tom Jones 75
6Tom Jones 10
7Billy Jack 36
8Billy Jack 75
9Billy Jack 75
Sheet1


Book2
AB
1James Smith100
2James Smith50
3Bob James45
4Jim Kraig30
5Tom Jones75
6Tom Jones10
7Billy Jack36
8Billy Jack75
9Billy Jack75
Sheet2


Book2
ABC
1JamesSmith100
2JamesSmith50
3BobJames45
4JimKraig30
5TomJones75
6TomJones10
7BillyJack36
8BillyJack75
9BillyJack75
Sheet3


Consider using this free tool instead to post some sample data.

 
Upvote 0
1) Are the names and amounts inside the same cell?
2) Which version of XL are you using?
3) Have you considered Pivot tables?
First name is in its own cell last name in its own cell and amount in its own cell. I use office 2007 and office 365 the excel version in each.

Thanks 🙏 for your help
 
Upvote 0
You may need to decide which version you want it to work in, for example the below options will work in 365 but not older version. There is a new function called GRPOUPBY in newer (demo) versions of Excel 365 but I don't have it:
Book1
ABCDEFGHIJK
1JamesSmith100JamesSmith150JamesSmith150
2JamesSmith50BobJames45BobJames45
3BobJames45JimKraig30JimKraig30
4JimKraig30TomJones85TomJones85
5TomJones75BillyJack186BillyJack186
6TomJones10
7BillyJack36
8BillyJack75
9BillyJack75
Sheet1
Cell Formulas
RangeFormula
E1:G5E1=LET(r,A1:C9, f,INDEX(r,,1),l,INDEX(r,,2),n,INDEX(r,,3), HSTACK(UNIQUE(f:l),SUMIFS(n,f,UNIQUE(f),l,UNIQUE(l))) )
I1:K5I1=HSTACK(UNIQUE(A1:B9),SUMIFS(C1:C9,A1:A9,UNIQUE(A1:A9),B1:B9,UNIQUE(B1:B9)))
Dynamic array formulas.


Could also use a pivot table?
 
Upvote 0
Hi, here's another couple of XL365 options pending the full roll out of GROUPBY().

Book1
ABCDEFGHI
1JamesSmith100James Smith = 150JamesSmith150
2JamesSmith50Bob James = 45BobJames45
3BobJames45Jim Kraig = 30JimKraig30
4JimKraig30Tom Jones = 85TomJones85
5TomJones75Billy Jack = 186BillyJack186
6TomJones10
7BillyJack36
8BillyJack75
9BillyJack75
Sheet1
Cell Formulas
RangeFormula
E1:E5E1=BYROW(UNIQUE(A1:B9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(C1:C9,A1:A9,TAKE(br,,1),B1:B9,TAKE(br,,-1)))))
G1:I5G1=LET(u,UNIQUE(A1:B9),HSTACK(u,BYROW(u,LAMBDA(br,SUMIFS(C1:C9,A1:A9,TAKE(br,,1),B1:B9,TAKE(br,,-1))))))
Dynamic array formulas.


@Georgiboy - I think your suggestion might run into problems if there are two people that share a first name or two people that share a surname.
 
Upvote 0
First name is in its own cell last name in its own cell and amount in its own cell. I use office 2007 and office 365 the excel version in each.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Here are three more ways to do it.
1. GroupBy formula
2. Pivot Table
3. Power Query

Book6
ABCDE
1NameValue
2James Smith100Billy Jack186
3James Smith50Bob James45
4Bob James45James Smith150
5Jim Kraig30Jim Kraig30
6Tom Jones75Tom Jones85
7Tom Jones10Total496
8Billy Jack36
9Billy Jack75
10Billy Jack75
Sheet1
Cell Formulas
RangeFormula
D2:E7D2=GROUPBY(A2:A10,B2:B10,SUM)
Dynamic array formulas.


Screenshot 2024-06-24 074047.png


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Total", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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