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
1	Media	Date	Circulation
2	Newspaper_01	1/01/2014	1000
3	Newspaper_02	2/01/2014	1000
4	Newspaper_02	2/01/2014	1000
5	Newspaper_02	3/01/2014	1000
6	Newspaper_03	5/01/2014	1000

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

 

[Fazza]

Suggest you consider non-formula approaches, especially if you have large datasets.

Such as a pivot table. Or query table (external data query).

Simpler is using advance filter. Such as select the source data range and apply advanced filter ALT-D-F-A to copy unique records only to a new location. Then sum the quantity.

 

[Harry Flashman]

Suggest you consider non-formula approaches, especially if you have large datasets.

Such as a pivot table. Or query table (external data query).

Simpler is using advance filter. Such as select the source data range and apply advanced filter ALT-D-F-A to copy unique records only to a new location. Then sum the quantity.

This is for a work colleague who produces a very simple monthly Excel report. The dataset is small, usually no more than a hundred rows. Normally I would calculate this using a pivot table, but in this case a single formula is exactly what we need (simplest, easiest and quickest solution). It will be a cinch for him to update the figure each month (in fact it will update automatically).

 

[Fazza]

Excellent. Happy weekend