Pivot -- want to use Max and Sum in same table

[Dave]

My apologies in advance. This is a little convoluted. As an overview, I
want a pivot table which can use the "Max" values for the Top axis values and
use "Sum" values for the Side axis values.

To start with, I have data that is similar to the following...but with
1000's of rows, many "Publsihers", "magazine titles", etc.

Publisher MagazineTitle Recipient of Promo Parent Company Promo Offers Made
Publisher_A Outdoor Smith_East Smith 10
Publisher_A Outdoor Smith_West Smith 8
Publisher_A Cars Smith_East Smith 15
Publisher_A Cars Smith_West Smith 4
Publisher_A Fashion Smith_East Smith 12
Publisher_A Fashion Smith_West Smith 5
....
....

Here is how to read this table. Publisher_A sends 10 promotions for the
magazine "outdoor" to Smith's east region office and 8 to the west region's
office. Since they are both going to the same company (Smith), I really only
care that I have 10 promos out to Smith for "Outdoor" versus 18, since the
promotions are overlapping for the same magazine....can't add them together.
Different magazines can be added.

Now, it is easy to create a pivot table which looks like the following.
Here,for instance, 15 is found using the "MAX" function in the pivot table
data field. Remember that the promos don't add, 15 is the max for "Cars" to
"Smith" in the table above.

SMITH
Cars 15
Fashion 12
Outdoor 10

Now the hard part... I really want the Pivot table to show the following.
Here, the promos for magazine titles under Publsiher_A, for instance, are
added together. This would, in summary, say that we have 37 promo offers
(15+12+10) out to SMITH.

SMITH
Publisher_A 37

It seems if one uses the "MAx" function in the Pivot Field, it then won't
add for subtotals, it will continue to use the "Max". If I created the last
pivot table, it would look like the following(where 15 is the largest, or
"Max" value in the list):

SMITH
Publisher_A 15

Sorry for the confusing problem.

Thanks in advance for any help.

--
DM

[Gary Rowe]

Have you tried putting the promos field in the data area a second time? You
can then do a sum and a max on the same field.
Gary

"Dave" wrote:

> My apologies in advance. This is a little convoluted. As an overview, I
> want a pivot table which can use the "Max" values for the Top axis values and
> use "Sum" values for the Side axis values.
>
> To start with, I have data that is similar to the following...but with
> 1000's of rows, many "Publsihers", "magazine titles", etc.
>
> Publisher MagazineTitle Recipient of Promo Parent Company Promo Offers Made
> Publisher_A Outdoor Smith_East Smith 10
> Publisher_A Outdoor Smith_West Smith 8
> Publisher_A Cars Smith_East Smith 15
> Publisher_A Cars Smith_West Smith 4
> Publisher_A Fashion Smith_East Smith 12
> Publisher_A Fashion Smith_West Smith 5
> ...
> ...
>
> Here is how to read this table. Publisher_A sends 10 promotions for the
> magazine "outdoor" to Smith's east region office and 8 to the west region's
> office. Since they are both going to the same company (Smith), I really only
> care that I have 10 promos out to Smith for "Outdoor" versus 18, since the
> promotions are overlapping for the same magazine....can't add them together.
> Different magazines can be added.
>
> Now, it is easy to create a pivot table which looks like the following.
> Here,for instance, 15 is found using the "MAX" function in the pivot table
> data field. Remember that the promos don't add, 15 is the max for "Cars" to
> "Smith" in the table above.
>
> SMITH
> Cars 15
> Fashion 12
> Outdoor 10
>
> Now the hard part... I really want the Pivot table to show the following.
> Here, the promos for magazine titles under Publsiher_A, for instance, are
> added together. This would, in summary, say that we have 37 promo offers
> (15+12+10) out to SMITH.
>
> SMITH
> Publisher_A 37
>
> It seems if one uses the "MAx" function in the Pivot Field, it then won't
> add for subtotals, it will continue to use the "Max". If I created the last
> pivot table, it would look like the following(where 15 is the largest, or
> "Max" value in the list):
>
> SMITH
> Publisher_A 15
>
> Sorry for the confusing problem.
>
> Thanks in advance for any help.
>
> --
> DM