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

Asked By Harry Flashman

16-Dec-09 02:45 AM

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.

16-Dec-09 03:26 AM

Hi

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

=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:D,ROW())=D2))

To calculate reach use this:

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

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

Hopes this helps.
...
Per

16-Dec-09 03:31 AM

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"
You can select a cell for the upper right corner of the pivot table. It will
look like
Select "Finish".
The template that comes up, I dragged and dropped "Date" into the cell above

You can rearrange the data to suit you.

hth

16-Dec-09 12:11 PM

I think you will 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, have not
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.

--
Biff
Microsoft Excel MVP

18-Dec-09 11:19 PM

't
IFT
ie
0)
nd
=3DB2),--($D$2:INDEX(D:D,ROW())=3DD2))