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.

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

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=527533

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" wrote:


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

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=527533

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=527533