Pivot Table - Sum Row Field Contents

[Guest]

Hi,
I have a pivot table that feeds from a large table of data on a neighbouring
sheet and have grouped rows by contents of particular columns by dropping
several column headers into the row data section.

I am now trying to obtain sub totals of how many of a particular group are
under each heading for example...how many advisory clients or discretionary
clients are there? Answer being 2 and 3.

Client Name Stock Name Cleint Type Stock Value
A Smith BP Advisory £2000
A Smith Shell Advisory £3000
A Smith RBS Advisory £2000
A Smith HSBC Advisory £3000
B Smith BP Advisory £5000
B Smith HSBC Advisory £2000
C Smith BP Discretionary £7000
C Smith BP Discretionary £2000

Thank you.

 

[robert111]

can you not just add advisory and discretionary as column headers?

Much better to create a sumproduct table in my opinion, if you attac
some dummy data I will show you (if u r unfamiliar

 

[Guest]

Hi Robert thanks for your reply,

My main problem is that the pivot table insists on either adding the values
(or counting them) rather than counting the number of advisory portfolios. A
simple count on advisory doesnt account for when one portfolio has more than
one line of stock as this simply counts the number of times the word avisory
occurs. I want it to count the number of different portfolios that are
advisory. In my original example there are 2 advisory portfolios and 1
discretionary, but so far i am unable to get the pivot table to do this. I
have thought about using the 'Calculated Field' and 'Calculated Item' options
but dont know if a). they are appropriate or b). how to use them.

Probably didnt explain what i was trying to do very well last time,
hopefully a bit clearer now.

Thanks

Andyk

 

[robert111]

in the pivot table make advisory and discetionary cloumn headers, and
make name a row header. Make the central counting field "sum of name"

So A Smith will only appear a maximum of once against advisory.

you will have problems if you have 2 clients with the same name