Use conditional summing in calculated field of pivot table

[Skysurfer]

Hi,


Is it possible to use a function containing text in a calculated field of a pivot table? I've tried several different functions, but text always generates zero values.


For example, can I create =IF(ACTION="Sell",SHARES,0) which would sum the field SHARES if the the corresponding field ACTION contains the word "Sell"?


I've tried the SUMIF function with the same result.


Lawrence

 

[dan_l]

I can't think of a way.


I'd be curious as to what the practical problem is. Usually when you have a situation that requires something like this, you either create a helper column in the source or you make clever use of filters or row heads in the pivot table.

 

[Skysurfer]

dan_l,


I have a database of stock transactions where one column (field) is labeled "Action" and another is labeled "Shares". Action can have have one of three entries: buy, sell, or dividend. And the shares field is the number of shares and it's always a positive number.


What I want is to have the pivot table calculate all the open positions in a security by summing all the buys and subtracting all the sells for each stock. Hence, SUMIF...or IF this then sum that.


Ideally, I would like to keep the database as clean as possible without helper columns. But from what you are saying I may not have a choice.


Filters???


Lawrence

 

[dan_l]

I think this is a pretty classic example of why you would use a helper column to interpret your value. It could be something as simple as =if(action="sell",shares * -1,shares). There may be something you could do in a pivot table, but I'm thinking this would be both easy and reliable.