Previous Topic Next Topic

Excel - Unique Filtering

Closed
fnkhan111 - May 11, 2010 at 09:34 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 11, 2010 at 11:14 AM
I have a two columns: Name and Amount. I want to apply a unique filter on the Name and retrieve the Sum of all the repeated Amounts.
How do I do this?

e.g.

A 5
A 4
A 1
B 20
B 40
C 0.3
C 0.4

should give me the result:

A 10
B 60
C 0.7

Please help. Thanks




2 replies

Answer 1 / 2
Trowa
May 11, 2010 at 09:53 AM
Hi fnkhan111,

Have you considered an array formula?

Try this:
=SUM(IF($A$1:$A$7="A",1,0)*($B$1:$B$7))
Confirm this formula by hitting Ctrl+Shift+Enter.
Drag the formula down two rows and replace the bold A by a B and C and you are done.
Remember to confirm the formula by hitting Ctrl+Shift+Enter after editing the formula.

Best regards,
Trowa
0
fnkhan111
May 11, 2010 at 10:13 AM
Hi Trowa,
Thanks - however, I had provided a very simplified version of my data set.
I have thousands of lines of data and there are about 500 unique entries.
Is there a way to sum each of the unique entities automatically?
Thanks
0
Answer 2 / 2
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 11, 2010 at 11:14 AM
This is also an array formula. You need to enter it as Trowa instructed earlier

=IF(ISERROR(MATCH(1,COUNTIF($A$1:A1,$A$1:$A$7),0)), "", SUMIF($A$1:$A$7, "=" & INDEX($A$1:$A$7,MATCH(1,COUNTIF($A$1:A1,$A$1:$A$7),0)),B:B ))

This is to entered row 1, else modify accordingly. Once entered, drag it down to last row
0