1. ## Sum unique (2003)

I have a calculation (I got it here of course) that counts unique group numbers - =sum(1/countif(range:range,range:range)) "ctrl alt enter" instead of just enter.
Now I was wondering if there was a way to sum a column that way. Everytime the group number changes, use just one of the numbers in the contract column to sum.
group number Contracts
0001 50
0001 50
0001 50
0001 50
0002 100
0002 100
0002 100
0003 10
0003 10
0004
0004
0004
0005 500
Total 660

2. ## Re: Sum unique (2003)

Let's say your data are in A1:B13. The following array formula (confirm with Ctrl+Shift+Enter) will sum unique entries in column B (unique defined by column A):

=SUM(B1:B13/COUNTIF(A1:A13,A1:A13))

The only difference with the count formula is that 1 has been replaced with B1:B13 (the range you want to sum).

3. ## Re: Sum unique (2003)

Could you simply use Data | Subtotals?

4. ## Re: Sum unique (2003)

Jerry,

I'm a big fan of subtotals, but how would that help to sum distinct entries only?

5. ## Re: Sum unique (2003)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Good point

6. ## Re: Sum unique (2003)

Thanks, I haven't had a chance to test it but I am sure it will work fine.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•