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

[Harry Flashman]

I have some data which looks like this:

Excel 2010

	A	B	C
Media	Date	Circulation
Newspaper_02

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #D9D9D9"]Newspaper_01[/TD]
[TD="bgcolor: #D9D9D9, align: right"]1/01/2014[/TD]
[TD="bgcolor: #D9D9D9, align: right"]1000[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #D9D9D9"]Newspaper_02[/TD]
[TD="bgcolor: #D9D9D9, align: right"]2/01/2014[/TD]
[TD="bgcolor: #D9D9D9, align: right"]1000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/01/2014[/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D9D9D9"]Newspaper_02[/TD]
[TD="bgcolor: #D9D9D9, align: right"]3/01/2014[/TD]
[TD="bgcolor: #D9D9D9, align: right"]1000[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #D9D9D9"]Newspaper_03[/TD]
[TD="bgcolor: #D9D9D9, align: right"]5/01/2014[/TD]
[TD="bgcolor: #D9D9D9, align: right"]1000[/TD]

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

 

[James006]

Hi,

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

=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]

One way:

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

 

[Harry Flashman]

Hi,

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

=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]

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

 

[James006]

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]

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]

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.

 

[James006]

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]

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.