Get Name From Array Using Sumif Formula

[steezytrees]

Hi, I have done a bunch of searching but just can't really figure out how to phrase what I am looking for, so hopefully someone here can help me out.

I have an array that has a bunch of names with dollar values of an account of theirs next to them. People have multiple accounts and come up on separate rows with the same name, but different values. I am trying to sum up the dollar values per person, which I have done with a sumif formula on a separate sheet. This works fine, but I cannot figure out how to then display next to the summed number, the person's name, and additionally have it aggregated to just one output per name. I need to be able to sort the original list and have the output remain the same.

Here's an example to help explain. If this is my data table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

My output table using =SUMIF($A$2:$A$6,A2,$B$2:$B$6)is then:

[TABLE="width: 500"]
<tbody>[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

And what I need is:

[TABLE="width: 500"]
<tbody>[TR]
[TD]John
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much in advance!

 

[T. Valko]

Hi, I have done a bunch of searching but just can't really figure out how to phrase what I am looking for, so hopefully someone here can help me out.

I have an array that has a bunch of names with dollar values of an account of theirs next to them. People have multiple accounts and come up on separate rows with the same name, but different values. I am trying to sum up the dollar values per person, which I have done with a sumif formula on a separate sheet. This works fine, but I cannot figure out how to then display next to the summed number, the person's name, and additionally have it aggregated to just one output per name. I need to be able to sort the original list and have the output remain the same.

Here's an example to help explain. If this is my data table:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]5
[/TD]
[/TR]
</TBODY>[/TABLE]

My output table using =SUMIF($A$2:$A$6,A2,$B$2:$B$6)is then:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[/TR]
</TBODY>[/TABLE]

And what I need is:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]John
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]9
[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks so much in advance!

You're doing it backwards.

You should first get the list of unique names and then apply the SUMIF formula to the names.

The easiest way to get the unique names is by using advanced filter. See this...

http://contextures.com/xladvfilter01.html#FilterUR

 

[steezytrees]

You're doing it backwards.

You should first get the list of unique names and then apply the SUMIF formula to the names.

The easiest way to get the unique names is by using advanced filter. See this...

http://contextures.com/xladvfilter01.html#FilterUR

That worked great, thanks for your help!

 

[T. Valko]

That worked great, thanks for your help!

You're welcome. Thanks for the feedback!