Finding the right function

[viljamil]

Hi,

I would need help finding the right function. If I have several names that are repeated many times, and there is a number after all of them, how could I combine these so that the name is repeated only once, and the function would count the sums that were after the repeated names after the name?

[#2]

@viljamil 

As far as I could gather from the text, you could use two possible methods for this.

Using a pivot table:

1. Open a new spreadsheet in Excel.
2. Enter your data into the spreadsheet with one column for names and one column for numbers.
3. Select all the data you entered.
4. Click on the “Insert” tab and select “Pivot Table”.
5. In the pivot table editor, select the name column as your “Rows” and the number column as your “Values”. Make sure to select “Sum” as the aggregation function for the number column.
6. The pivot table will now display each unique name once with the sum of all the numbers associated with that name.

Using a formula:

1. Open a new spreadsheet in Excel.
2. Enter your data into the spreadsheet with one column for names and one column for numbers.
3. In a new column, enter each unique name once.
4. In the cell next to the first unique name, enter the formula =SUMIF(range, criteria, sum_range), where range is the range of cells containing the names, criteria is the cell containing the unique name, and sum_range is the range of cells containing the numbers. For example, if your names are in column A and your numbers are in column B, and your first unique name is in cell D2, you would enter =SUMIF(A:A,D2,B:B) in cell E2.
5. Drag the formula down to fill in the rest of the cells next to each unique name.

Both methods will give you a list of each unique name with the sum of all the numbers associated with that name.

If this is not what you are looking for, please include the following info to help others answer your question (click on link):

Welcome to your Excel discussion space!

I hope this helps! 

NikolinoDE

I know I don't know anything (Socrates)

[#3]

there is no magic function to do this. A formula can be created to do it. For example I just remember i wrote a LAMBDA function that will extract numbers from a string of text so that could be used but is probably over-kill. For example is there a consistent format for the name number combination? Is there always a character like - or : in between or at least a space? What about the names, are they single names Jane-3 or could they be multiple like Jane Doe 5
A sample sheet with example data would make it much easier for to answer the question.
that all said I created a formula to do it based on spaces:

=LET(in, A1:A9,
numbers, TOCOL(--TEXTSPLIT(TEXTJOIN(";",1,TRIM(in)),  " ", ";",0,,""),3),
names, TRIM(SUBSTITUTE(in,numbers,"")),
nList, UNIQUE(names),
REDUCE({"names","amt"},nList,LAMBDA(p,q, VSTACK(p,HSTACK(q,SUM(FILTER(numbers,names=q,0)))))))

NOTE: I think Niko above assumed you mean you have names in 1 column and values in the next column.  Those solutions above work well for that.  I assumed a more difficult problem where the cell is text with both the name and number in it:

Mary 5

Sue Beth 18

see attached.