Excel - Unique Filtering

Closed
fnkhan111 - May 11, 2010 at 09:34 AM
rizvisa1 Posts 4478 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




Related:

2 responses

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
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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