Thanks for the response and suggestion. I tried both and ran into the
same problem as I originally had - the counts of the Catg fields are
inaccurate. Because the Catg field is listed next to each item, the
number of Catg's in the Pivottable always equals the number of items.
The Sales person also only receives one commision per Acct. So, I
suppose what I really need is a way to total the number of unique
Acct's, per Sales person, listed by Catg, and only for Items that match
a list of descriptions. Any suggestions on how to accomplish this?
RWS wrote:
If you go into pivot table wizard, Layout, try the following

Put "Count of item" into Data field
Put "Sales" into Row Field
Put "Item" into Column field

From here you have two options

1) leave it like this and create a second pivot table exactly the same but
with Catg instead of Item in the column field. Then you have 2 pivot tables
to get your data

2)If you and your manager are happy to play around with the table, you can
be cleverer, and put the Catg into the page field on the first table. As and
when you want to see the Catg analysis, drag it into the column field next to
item (try to left and right of it, whichever is more useful layout), then you
can double click to drilldown into the analysis down further, or you can pull
drag item up into the top left corner of the sheet to just leave Catg.

Have a play with putting multiple fields into row and columns, it can be
useful especially on bigger datasets, and all the data is to hand when you
need it rather than having multiple tables
--
RWS


"Teeroi" wrote:

Hi all - I've received some terrific info here before, and after 2 days
of struggling have decided to try again. Here's my problem:

I have a PivotTable tied to an ODBC data source (AS400 Table). The
data is updated daily. A simplified version of the table is:

A B C D E
1 Sales Acct Dr Item Catg
2 Bob 100 Dr. A Zig Res
3 Bob 100 Dr. A Zag Res
4 John 101 Dr. B Zig Res
5 Tim 121 Dr. C Zog Ox
6 Tim 121 Dr. C Zag Res
7 Bob 133 Dr. D Zog Ox
8 Tim 141 Dr. E Zig Res
9 John 167 Dr. G Zog Ox
10 John 167 Dr. G Zag Res


My PivotTable appears as follows:

Sales Dr Acct Catg Item Total
Bob 3
Dr. A 2
100 2
Res 2
Zig 1
Zag 1
Dr. D 1
133 1
Ox 1
Zog 1

John 3
Dr. B 1
101 1

Res 1
Zig 1
Dr. G 2
167 2
Ox 1
Zog 1
Res 1
Zag 1

(I hope that doesn't look to confusing).

What I need is to calculate - based on the PivotTable data - how many
of each category (Res, Ox) each sales person (Bob, John) has sold. I
also need to exclude items that aren't tracked (Zig's). The sales
manager viewing the spreadsheet needs to see all of the sales,
regardless of whether the item is being tracked. So, in the above
example Bob would have 1 "Res" sale and 1 "Ox" sale. John would also
have1 "Res" sale and 1 "Ox" sale, because "Zigs" aren't tracked. If
this makes sense to anyone, I'd appreciate any input you could give.
Thanks in advance.