Summing unique values (where "unique" depends on multiple criteria")

[Harry Flashman]

I have three columns, date, publication, reach

Date Publication Reach
1/02/2009 Herald Sun 200
1/02/2009 Herald Sun 200
3/02/2009 Herald Sun 200
3/02/2009 Herald Sun 200
5/02/2009 Herald Sun 200
6/02/2009 Herald Sun 200
7/02/2009 Herald Sun 200
8/02/2009 Herald Sun 200
9/02/2009 Herald Sun 200
9/02/2009 Herald Sun 200
1/02/2009 The Age 100
1/02/2009 The Age 100
1/02/2009 The Age 100
5/02/2009 The Age 100
5/02/2009 The Age 100
7/02/2009 The Age 100
7/02/2009 The Age 100
8/02/2009 The Age 100
9/02/2009 The Age 100
9/02/2009 The Age 100

The total of the reach column is 3000. The Herald Sun's total is 2000
and The Age's total is 1000
Now I would like to calculate the unique reach for each publication.
That is I only sum the reach once per day. If the publication is
listed more than once for a given day I only include the first
instance in the total.
Thus the unique reach for the Herald Sun would be 1400, and The Age
would be 500.

Would anyone be able to tell me how to calculate the unique reach for
each publication using a formula?
In this example there are two conditions namely date and publication.
I would also be interested in calculating unique reach with an extra
condition, "headline". Thus if two instance of a publication occur on
the same day, but with different headlines they will be summed.

I would be extremely grateful if anyone coud steer me in the right
direction Which funcition will solve this? I have experiment with
SUMIF and SUMIFS but I have not yet had success,
Also if it is possible to figure this out with a pivot table I would
be interested to know this too.

 

[Per Jessen]

Hi

In an helper column, insert this formula and copy it down as required (ie
D221):

=COUNTIF($A$2:INDEX(A:A,ROW()),A2)

Then use this formula to calculate the reach:

=SUMPRODUCT(--($D$2:$D$100=1),--($B$2:$B$100="Herald Sun"),C2:C100)
=SUMPRODUCT(--($D$2:$D$100=1),--($B$2:$B$100="The Age"),C3:C101)

Then if we insert Headline in column D, use column E as helper column and
use this formula:

=SUMPRODUCT(--($A$2:INDEX(A:A;ROW())=A2),--($B$2:INDEX(B:B,ROW())=B2),--($D$2:INDEX(D,ROW())=D2))

To calculate reach use this:

=SUMPRODUCT(--($E$2:$E$100=1),--($B$2:$B$100="Herald
Sun"),--(D2100="Headline"),C2:C100)

In all formulas Publication and Headline can be substituted with a cell
reference.

Hopes this helps.
....
Per

 

[FloMM2]

Harry,
This is what I came up with to solve your question:
First, I added another column - in cell D1 I put "Daily Reach" for the
totals for each day.
In cell D2 the formula "=C2". In D3 the formula, "=IF(A3=A2,"",C3)".
I then copied this down to and including cell D21.

I then select "Data", "Pivot Table and PivotChart Report".
Select the whole chart, A1 thru D21 (in my case).
Except the defaults to the questions, "Where is the data you want to analyze?"
Should be, "Microsoft Office Excel list or database"
AND "What kind of report do you want to create?"
Should be "Pivot Table"
Select "Next".
Question, "Where is the data that you want to use?"
Range: "$A$1:$D$21" (or the top right cell where your data is to the bottom
left cell).
Select "Next".
Question, "Where do you want to put the Pivot Table report?"
Either "New Worksheet"
"Existing Worksheet"
You can select a cell for the upper right corner of the pivot table. It will
look like
"Sheet1!$E$2", if you selected cell E2 on sheet1.
Select "Finish".
The template that comes up, I dragged and dropped "Date" into the cell above
"Drop Row Fields Here" . The cell next to it (F2) I draggged and dropped,
"Publication". I dragged and dropped the "Daily Reach" into the
"Drop Data Items Here".

You can rearrange the data to suit you.

hth

 

[T. Valko]

In an helper column, insert this formula and

copy it down as required (ie D221):
=COUNTIF($A$2:INDEX(A:A,ROW()),A2)

I think you'll have to change that to:

=SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))

Without using a helper column (although that may be more efficient, haven't
tested it):

Data in the range A2:C21

E2 = Herald Sun
E3 = The Age

Array entered** in F2 and copied down to F3:

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

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

Assumes there are no empty cells.

 

[Harry Flashman]

I think you'll have to change that to:

=SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))

Without using a helper column (although that may be more efficient, haven't
tested it):

Data in the range A2:C21

E2 = Herald Sun
E3 = The Age

Array entered** in F2 and copied down to F3:

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

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

Assumes there are no empty cells.

Thank you to all who have replied. I'll conduct some experiments with
all your solutions.
Bill's solution (or what he added to Per Jensen's solution) looks
promising.
The media I provided was just an example, in fact there will hundreds
of media.
Ideally the solution will work the same way regardless of how the data
is sorted, which means Flomm2's idea might not work for me. Still it
has given me something to play with and learn from
Thank you all. Much appreciate it.