Sum unique columns

Check out this link.

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

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:

Formula:

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.

Formula:

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

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

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.

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.