Excel - Unique FilteringClosed

Question

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




Configuration: Windows XP / Safari 532.5

Trowa · Answer

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

rizvisa1 · Answer

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

Excel - Unique Filtering

2 responses

Similar discussions

Subscribe To Our Newsletter!