Remember Me?

#1
January 25th 06, 09:01 PM posted to microsoft.public.excel.misc
 Posts: n/a
Calculated field in Pivot Table

I have a dataset like the one below (not sure if formatting will come
out right in the posting). The example below is just a simplication of
my actual dataset.
Group Series Status
A A1 Open
A A2 Open
A A3 Unknown
A A9 Closed
A A10 Open
B B1 Closed
B B4 Closed
B B5 Closed
B B6 Closed
C C1 Unknown
D D1 Closed
D D2 Closed
E E1 Open
E E2 Open

What I want to do is count how many Groups fall into the status Open
bucket, how many into Closed, etc. Now because Group A has a Series in
each of the three possible Statuses, it falls into all three buckets.
Group E, on the other hand, only falls into one. If I pull Status into
a Pivot as a Column Field and then do a Count of Group, the values will
actually represent the number of Series in a given Status because the
value A appears in the dataset once for each corresponding Series in
that Group. Have I lost you yet? My hypothesis is that there is a
creative way to use calculated fields in a pivot to get it to show that
there are 2 groups showing Open, 2 showing Unknown, and 3 showing
Closed. I already know I could arrange the pivot in such a way that
=COUNTA( ) in the adjacent cells would give me the same thing, but I'm
looking for a way of doing it in the pivot for reason not worth wasting

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post benb Excel Discussion (Misc queries) 2 January 25th 06 10:48 PM baabaa Excel Discussion (Misc queries) 1 November 25th 05 09:08 AM fhaberland Excel Discussion (Misc queries) 0 August 2nd 05 07:47 PM Angus Excel Discussion (Misc queries) 0 July 30th 05 05:06 AM Phoenix71555 Excel Discussion (Misc queries) 1 February 28th 05 12:25 AM

All times are GMT +1. The time now is 07:24 PM.