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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 11, 2010 at 11:14 AM
Related:
- Excel - Unique Filtering
- Excel mod apk for pc - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- Safesearch filtering is on - Guide
- Kernel for excel - Download - Backup and recovery
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
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 11, 2010 at 11:14 AM
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
=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
May 11, 2010 at 10:13 AM
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