Find unique value and sum results.

[joelnichols]

Brief history. Left job about 15 years ago where I used Excel a little. Back to using it now but forgot almost everything I knew (ugh). So forgive the simple problems please . I.E , cant figure out how to post my sheet or download the add-in. So my question is, Col A has names (listed multiple times), B has amount used. I want to look for name and sum the amount used. Whats the best (easiest)way to do that? Also need to exclude the header from the list.

 

[awoohaw]

=SUMIF(ColA,name,ColB)

so if the name your summarizing is in cell C2, and your columns go to row 100, starting at row 2, in cell D2 you could put:

=SUMIF($A$2:$A$100,C2,$B$2:$B$100)

 

[joelnichols]

=SUMIF(ColA,name,ColB)

so if the name your summarizing is in cell C2, and your columns go to row 100, starting at row 2, in cell D2 you could put:

=SUMIF($A$2:$A$100,C2,$B$2:$B$100)[
[/QUOTE]

Perfect, but I got ahead of myself on that. How would I get the names for Col A from a different sheet and have it list in alphabetical order? Or should I do a new post for that question?

 

[awoohaw]

If this is a one time thing, just copy and paste. Then use the AutoFilter tool and then sort.

But, that should not make any difference, SUMIF does not require sorted data.

If it is a recurring thing, then you can record a macro, then edit it to be more ubiquitous.

Later versions of EXCEL have a SORT Function that would make this very easy.

If you do need to have formula or macro for the sorting then I suggest making another post. But make it clear so it seems to be an entirely different question.

And with what you're asking both workbooks need to be open when doing the data grab, and you'd probably need to convert to values each time so you don't have to worry about the 2nd workbook always needing to be open. But that opens another can of worms with data becoming stale, etc.

 

[joelnichols]

If this is a one time thing, just copy and paste. Then use the AutoFilter tool and then sort.

But, that should not make any difference, SUMIF does not require sorted data.

If it is a recurring thing, then you can record a macro, then edit it to be more ubiquitous.

Later versions of EXCEL have a SORT Function that would make this very easy.

If you do need to have formula or macro for the sorting then I suggest making another post. But make it clear so it seems to be an entirely different question.

And with what you're asking both workbooks need to be open when doing the data grab, and you'd probably need to convert to values each time so you don't have to worry about the 2nd workbook always needing to be open. But that opens another can of worms with data becoming stale, etc.

Its all 1 book. Importing data daily or weekly to sheet 3 and it may have same name multiple times. Trying to get all unique names to sheet 1 ColA in alphabetical order and add the corresponding values to ColB.

 

[awoohaw]

Okay, only 1 workbook is helpful. But, still you need to record a macro.
Start Recording Macro.
Goto Sheet 3, select column A
Goto Sheet 1 select cell A1
Paste
Select Column A
right click
click Sort
Stop Recording Macro.

If sort isn't in the ALT-MENU then use the sort tool in the DATA ribbon.

 

[joelnichols]

Went with Index, match and seems good. Thanks

 

[Peter_SSs]

Went with Index, match

Yet you have marked post #6, which does not mention index/match as the solution?

Could you explain how you used index match to achieve your result as it might help future readers?

Trying to get all unique names to sheet 1 ColA in alphabetical order and add the corresponding values to ColB.

If you create a Pivot Table from the original data it will automatically give a sorted list of unique names and the totals for each name. At least it did for me.

joelnichols.xlsm
	A	B
1	Name	Amount
2	Ann	6
3	Bill	2
4	Sam	5
5	Sam	3
6	Bill	2
7	Jen	1
8	Jen	5
9	Ann	4
10	Sam	2
11	Ken	3
12	Eva	4
13	Bill	5
Sheet2

joelnichols.xlsm
	A	B
1	Row Labels	Sum of Amount
2	Ann	10
3	Bill	9
4	Eva	4
5	Jen	6
6	Ken	3
7	Sam	10
8	Grand Total	42
Sheet1