Sum Unique Values

M

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #B8CCE4;;">Route #</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #B8CCE4;;">Cust #</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #B8CCE4;;">Cases</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #B8CCE4;;">Route # </td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #B8CCE4;;">Total Cases</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">501</td><td style="text-align: center;;">1</td><td style="text-align: center;;">245</td><td style="text-align: right;;"></td><td style="text-align: center;;">501</td><td style="text-align: center;;">514</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">501</td><td style="text-align: center;;">2</td><td style="text-align: center;;">24</td><td style="text-align: right;;"></td><td style="text-align: center;;">502</td><td style="text-align: center;;">381</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">501</td><td style="text-align: center;;">1</td><td style="text-align: center;;">245</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">502</td><td style="text-align: center;;">5</td><td style="text-align: center;;">51</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">502</td><td style="text-align: center;;">5</td><td style="text-align: center;;">51</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">502</td><td style="text-align: center;;">6</td><td style="text-align: center;;">74</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">502</td><td style="text-align: center;;">7</td><td style="text-align: center;;">256</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">501</td><td style="text-align: center;;">3</td><td style="text-align: center;;">24</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">501</td><td style="text-align: center;;">4</td><td style="text-align: center;;">96</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">501</td><td style="text-align: center;;">3</td><td style="text-align: center;;">24</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">501</td><td style="text-align: center;;">3</td><td style="text-align: center;;">24</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">502</td><td style="text-align: center;;">5</td><td style="text-align: center;;">51</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">501</td><td style="text-align: center;;">8</td><td style="text-align: center;;">125</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">501</td><td style="text-align: center;;">8</td><td style="text-align: center;;">125</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">501</td><td style="text-align: center;;">8</td><td style="text-align: center;;">125</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">501</td><td style="text-align: center;;">1</td><td style="text-align: center;;">245</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">501</td><td style="text-align: center;;">1</td><td style="text-align: center;;">245</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

I have a list of Routes with Customers Numbers and Cases Sold. The Customer Number and Cases Sold may be repeated several times. In column F I need the Total Cases Sold by Route. So I need to Sum the Cases by Route counting each Customer only once.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
If using Excel 2007, why not use the Remove Duplicates feature to get rid of your duplicate lines?
 
Upvote 0
markoakes said:
Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center; TEXT-DECORATION: underline">Route #</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center; TEXT-DECORATION: underline">Cust #</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center; TEXT-DECORATION: underline">Cases</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center; TEXT-DECORATION: underline">Route # </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center; TEXT-DECORATION: underline">Total Cases</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">245</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">514</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">502</TD><TD style="TEXT-ALIGN: center">381</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">245</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">502</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">51</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">502</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">51</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">502</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">74</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">502</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">256</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">96</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">502</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">51</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">125</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">125</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">125</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">245</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">245</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1​




I have a list of Routes with Customers Numbers and Cases Sold. The Customer Number and Cases Sold may be repeated several times. In column F I need the Total Cases Sold by Route. So I need to Sum the Cases by Route counting each Customer only once.
Click to expand...
Try this...

Book1
ABCDEF
25011245_501514
3501224_502381
45011245___
5502551___
6502551___
7502674___
85027256___
9501324___
10501496___
11501324___
12501324___
13502551___
145018125___
155018125___
165018125___
175011245___
185011245___
Sheet3

This array formula** entered in F2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$18=E2,MATCH(B$2:B$18,B$2:B$18,0)),ROW(B$2:B$18)-ROW(B$2)+1)>0,C$2:C$19))

** 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.

Note in the formula how the sum range is one cell longer than the criteria ranges.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
693
John_w
J

Forum statistics

Threads
1,224,606
Messages
6,179,866
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top