Sum unique columns

[oyvzolyx]

Hi, i have a table:

Name Apples
Ken 2
James 5
James 5
John 3

Without pivot tables, I want the formula of total sum of apples of every name but not duplicates, in this case: 10.
How can this be done?

[AlKey]

Check out this link.

http://www.excelforum.com/excel-prog...ml#post4131303

[CallumWebley]

Cant you just remove duplicates. and choose names

[oyvzolyx]

I'd rather not remove duplicates, because it's in a table with other values, example:

Name Apples Oranges
Ken 2 5
James 5 8
James 5 1
John 3 3

Ok thanks, I will check out that link.

[oyvzolyx]

Is this possible with a formula? (without VBA)

[newdoverman]

This problem doesn't make a whole lot of sense because when you had only Names and Apples there were duplicates but as soon as you added Oranges, the duplicate that existed not longer exists (as a complete record)
With only apples this works:

Please Login or Register  to view this content.

	A	B	C
1	Name	Apples	Apples Sum
2	Ken	2	2
3	James	5	5
4	James	5
5	John	3	3

If you want to show the sum of Oranges separately as a unique count then possibly this will work.

Please Login or Register  to view this content.

	A	B	C	D	E
1	Name	Apples	Oranges	Apples Sum	Oranges Sum
2	Ken	2	5	2	5
3	James	5	8	5	8
4	James	5	1		1
5	John	3	3	3	3
6	John	3	3
7	Sam	4	5	4	5
8	Sam	2	5	2

[AlKey]

Use this formula to get unique names list

Enter in E2 and copy down

=IFERROR(INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF(E$1:E1,$A$2:$A$5),,),)),"")

enter formula in F2 and and pull it to the right and then down

=SUMIFS(INDEX($B$2:$C$5,0,MATCH(F$1,$B$1:$C$1,0)),$A$2:$A$5,$E2)

Data Range

	A	B	C	D	E	F	G
1	Names	Apples	Oranges		Uniqe names	Apples	Oranges
2	Ken	2	5		Ken	2	5
3	James	5	8		James	10	9
4	James	5	1		John	3	3
5	John	3	3

[Tony Valko]

Try this...

Data Range

	A	B	C	D
1	Name	Apples		Total
2	Ken	2		10
3	James	5
4	James	5
5	John	3
6	------	------	------	------

This array formula** entered in D2:

=SUM(IF(FREQUENCY(MATCH(A2:A5&B2:B5,A2:A5&B2:B5,0),ROW(B2:B5)-ROW(B2)+1),B2:B5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[oyvzolyx]

Thanks all

[Tony Valko]

You're welcome!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.