# Calculate Unique Circulation with a Single Formula (sum unique records)

#### Harry Flashman

##### Active Member
I have some data which looks like this:

Excel 2010
ABC
2Newspaper_011/01/20141000
3Newspaper_022/01/20141000
4Newspaper_022/01/20141000
5Newspaper_023/01/20141000
6Newspaper_035/01/20141000

<tbody>
</tbody>

The sum of circulation values above is 5000.
The unique circulation is 4000.

Notice that Newspaper_02 appears on three three rows, but the second row does not count because it has the same date (each row represents a newspaper article, on January 2nd Newspaper_02 had two articles. The circulation for the second article does not count because it occurred in the same issue as the first article.

I want to calculate the unique circulation with a single formula, if this is possible. If it is not possible I have other ways, but I would like to find out if there is a way of doing with a single formula.

Do any of you Excel geniuses out there see a way that this might be achieved? I would greatly appreciate any help with this. Cheers.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### James006

##### Well-known Member
Hi,

With an array formula, use ctrl shift enter (not just enter)

Code:
``=SUM(IF(FREQUENCY(IF((A2:A6)&(B2:B6)<>"",MATCH((A2:A6)&(B2:B6),(A2:A6)&(B2:B6),0)),ROW(A2:A6)-ROW(A2)+1),C2:C6))``

All credit goes to Aladin ...!!!

HTH

#### TMShucks

##### Active Member
One way:

=SUMPRODUCT((\$C\$2:\$C\$6),--(\$B\$2:\$B\$6<>\$B\$1:\$B\$5))

#### Harry Flashman

##### Active Member
Hi,

With an array formula, use ctrl shift enter (not just enter)

Code:
``=SUM(IF(FREQUENCY(IF((A2:A6)&(B2:B6)<>"",MATCH((A2:A6)&(B2:B6),(A2:A6)&(B2:B6),0)),ROW(A2:A6)-ROW(A2)+1),C2:C6))``

All credit goes to Aladin ...!!!

HTH

Brilliant! I had a feeling it would be something like that. I could find plenty of examples for counting unique values, but I was unsure how to adjust them to my purpose. Thanks.

#### Harry Flashman

##### Active Member
One way:

=SUMPRODUCT((\$C\$2:\$C\$6),--(\$B\$2:\$B\$6<>\$B\$1:\$B\$5))

Edit: ahh... not quite. That formula only considers the B and C columns. It need to sum unique records, including the the A column as well. It is still a pretty cool formula though. I will put that one in my bag of tricks, once I have figured it out (might need to take a closer look at how it works). Thank you

Last edited:

#### James006

##### Well-known Member
That is great too. And it is not even an array formula. Nice.

You should be careful with the column A ... changing cell A4 will have no effect on this sumproduct formula ...

HTH

#### pgc01

##### MrExcel MVP
Hi

I think you can do without the Frequency() in the formula. Try this array formula:

=SUM(IF(A2:A6&B2:B6<>"",IF(MATCH(A2:A6&B2:B6,A2:A6&B2:B6,0)=ROW(A2:A6)-ROW(A2)+1,C2:C6)))

#### TMShucks

##### Active Member
OK, maybe this:

=SUMPRODUCT((\$C\$2:\$C\$6),--(\$A\$2:\$A\$6&\$B\$2:\$B\$6<>\$A\$1:\$A\$5&\$B\$1:\$B\$5))

This concatenates the publication and date and compares it to the previous row.

Note that for it to work, the data needs to be sorted into publication and date order.

Last edited:

#### James006

##### Well-known Member
Hi

I think you can do without the Frequency() in the formula. Try this array formula:

=SUM(IF(A2:A6&B2:B6<>"",IF(MATCH(A2:A6&B2:B6,A2:A6&B2:B6,0)=ROW(A2:A6)-ROW(A2)+1,C2:C6)))

Thanks pgc01 !!!
It is always nice to learn new tricks...

Cheers

#### Harry Flashman

##### Active Member
Thanks to everyone for helping me out with this question. I now have a couple of methods that I can use and learn from. Cheers.

Replies
6
Views
275
Replies
0
Views
515
Replies
3
Views
180
Replies
9
Views
261
Replies
0
Views
143

1,191,527
Messages
5,987,106
Members
440,079
Latest member
MarchePR

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

### Which adblocker are you using?

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

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