ANOTHER conditional sum in a pivot table question 1

[elsenorjose]

Is it possible to create a calculated field or item in a pivot table based on multiple criteria? Here's a stripped down sample of my data using the relevant fields:

Type Plays/DLs
Live 49
Live 3
Live 5727778
Live 1
Live 791
Live 1
Live 4
Live 5
Live 17
OD 903
OD 5425
OD 1
OD 1
OD 1
OD 1
OD 1
OD 2
Podcast 991
Podcast 91
Podcast 4399
Podcast 3259
Podcast 241
Podcast 1
Podcast 851
Podcast 2

I need to add up the Plays/DLs values but only where type is OD or Podcast. I'm not sure I can create it in my source data tab and have been trying to create a calculated field in my pivot so I can summarize it across other levels (my data also includes markets, station names, and type of music formats).

 

[SkipVought]

Hi,

I'd add a new column for this calculation. THEN pivot.

Skip,

_[red][/red]

 

[elsenorjose]

Skip, I can't. You gave me that advice for another problem I had evaluating just one criteria and that worked perfectly, thanks again. But in this case, I'm evaluating 2 criteria and can't figure out how I'd create a new column.

In my data example, adding a new column would result in:

Type Plays/DLs OD+Podcast
Live 49 ?
Live 3 ?
Live 5727778 ?
Live 1 ?
Live 791 ?
Live 1 ?
Live 4 ?
Live 5 ?
Live 17 ?
OD 903 ?
OD 5425 ?
OD 1 ?
OD 1 ?
OD 1 ?
OD 1 ?
OD 1 ?
OD 2 ?
Podcast 991 ?
Podcast 91 ?
Podcast 4399 ?
Podcast 3259 ?
Podcast 241 ?
Podcast 1 ?
Podcast 851 ?
Podcast 2 ?

What exactly is the formula for the new column?

I'm playing around with something like:

=SUMIF(Type,"<>Live",Plays/DLs)

But so far no luck. But I think this might get me closer.

Your thoughts on that formula?

 

[SkipVought]

[tt]
=SUMIF(Type,"OD",Plays_DLs)+SUMIF(Type,"Posdcast",Plays_DLs)
[/tt]


Skip,

_[red][/red]

 

[elsenorjose]

Thanks Skip. The formula's not working for a calculated field. All I get is 'You have an error in your formula' but no trace error or additional help. If I use the formula to create a new column in my data source, the cell I paste it in gives me the grand total for every row.

I'll keep trying. Thanks for the help

 

[SkipVought]

In the Type argument, you must have the RANGE that contains the Type Data

In the Plays_DLs argument, you must have the RANGE that contains the Plays/DLs Data

The ROWS values must be identical.

I prefer to use Named Ranges. The expression returns 6335 as posted.

Skip,

_[red][/red]

 

[elsenorjose]

Yeah, I use named ranges too. Much cleaner for when the data grows.

So, there's a problem with that logic I can see right away. 6335 is the total for OD only. My total should be 16710 which is the sum of OD and Podcast Plays. And I still can't create a calculated field so I can insert it into my pivot. My pivot should basically look like this once all is said and done. Again, this is a minimal set of data. There may be other groupings besides market.


Sum of Plays/DLs Type
Market Live OD Podcast Grand Total Podcast + OD
ALLENTOWN-PA 54 905 334 1293 1239
AMES-IA 799 5428 4400 10627 9828
ANCHORAGE-AK 5727796 2 5101 5732899 5103
Grand Total 5728649 6335 9835 5744819 16170

 

[SkipVought]

I guess I copied the sum for OD only.

I get 16170 with the example you just posted using...
[tt]
=SUMIF(Type,"OD",Plays_DLs)+SUMIF(Type,"podcast",Plays_DLs)
[/tt]

Actually the new column ought to be...
[tt]
=if(or(type="OD",type="podcast"),Plays_DLs,0)
[/tt]


Skip,

_[red][/red]

 

[elsenorjose]

Thanks again Skip. That's one for the stick notes collection

 

[yogia]

Hi elsenorjose:

Unless I am missing something here, if you are using a PivotTable, with TYPE as Row_field and SUM of Plays/DLs as Data, then in the PivotTable for Type you will see a DropDown arrow and you can uncheck Live so that only OD and Podcast remain checked ... then in the PivotTable you will see the totals for ...

OD          = 6335
Podcast     = 9835
Grand Total =16170

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[elsenorjose]

Thanks Yogi but I need to display all values AND only the sum of OD and Podcast. So there will be 4 totals basically: OD, Live, Podcast, and OD+Podcast.

 

[pbrodsky]

You don't need to create the totals if you are doing a pivot table - let the pivot table do the calculations. Create a new columm with a formula like =if(or(a3="Podcast",a3="OD"),"OD+Podcat","Other) and use that as a category in the pivot table - then you can easily get a subtotal based on it and modify the formula to include other categories. If it get complicated, make a table of the categories and use a VLOOKUP.

 

[elsenorjose]

Thanks everyone for the replies. The formula Skip provided was perfect for my needs!