Calculate PivotTable Item Totals Based On Cell Contents?

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.

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.
>
>

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.
> >
> >

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.
> >
> >

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.
> >
> >

Ok then lets try this. Its not an ideal solution, but it should give you the
answer

Open your pivot table wizard and set as follows

In column Field put "Sales"
In Data field put "item" and ensure field properties are 'count of'
In the row field put Acct at the top, Cat next and then item, so you have 3
variable in the row field.

Close the wizard and go back to the table
This shows all sales by acct/salesman
In the Cat and item boxes you can deselect the ones you don't want, e.g.
blank catg, and zigs
This will leave you only sales of ox/res and not include any zigs

Of course this still shows you total sales for each salesman, by account.
Essentially from here what you want to know for each salesman is "how many
rows(accounts) have a number (one or more sale) in them?

If you use this formula below the pivot table in each column, and set the
d7:d9 bit to a column of your pivot table (data part only not row/column
headers)
=ROWS(D7:D9)-COUNTBLANK(D7:D9).Copy for each column

(This bit is not so clever i am afraid, because as your table changes you
will have to do this change manually each time to ensure it counts all
rows,(this will change with acct quantities). Somebody may be able to suggest
a neater way of doing this last bit)

The number it returns is the number of different accounts that the salesman
has sold to, including only those categories and products you selected.








--
RWS


"Teeroi" wrote:

> 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.
> > >
> > >
>
>

Thanks again - thats definitely getting closer. As I have it now, it
sorts by sales person first, then account, then catg, then item - with
the item also listed in the data field. I created a formula to count
down the catg row and subtotal the occurences of the individual
categories.

=COUNTIF(D:D,"Res")

The only problem is, you have to manually hide the rest of the sales
people if you want to see the totals for an individual salesperson. Is
there a way count down from the first sales persons name, but only
until the formula finds another non-blank cell? Something like:

=COUNTIF(D:D,"Res") & (A:13,"Bob") & (A:A,Non-Blank) ??

Or maybe a way to involve the GetPivotData command?


RWS wrote:
> Ok then lets try this. Its not an ideal solution, but it should give you the
> answer
>
> Open your pivot table wizard and set as follows
>
> In column Field put "Sales"
> In Data field put "item" and ensure field properties are 'count of'
> In the row field put Acct at the top, Cat next and then item, so you have 3
> variable in the row field.
>
> Close the wizard and go back to the table
> This shows all sales by acct/salesman
> In the Cat and item boxes you can deselect the ones you don't want, e.g.
> blank catg, and zigs
> This will leave you only sales of ox/res and not include any zigs
>
> Of course this still shows you total sales for each salesman, by account.
> Essentially from here what you want to know for each salesman is "how many
> rows(accounts) have a number (one or more sale) in them?
>
> If you use this formula below the pivot table in each column, and set the
> d7:d9 bit to a column of your pivot table (data part only not row/column
> headers)
> =ROWS(D7:D9)-COUNTBLANK(D7:D9).Copy for each column
>
> (This bit is not so clever i am afraid, because as your table changes you
> will have to do this change manually each time to ensure it counts all
> rows,(this will change with acct quantities). Somebody may be able to suggest
> a neater way of doing this last bit)
>
> The number it returns is the number of different accounts that the salesman
> has sold to, including only those categories and products you selected.
>
>
>
>
>
>
>
>
> --
> RWS
>
>
> "Teeroi" wrote:
>
> > 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.
> > > >
> > > >
> >
> >

Teeroi

It sounds to me like you have the Sales person in the row field (i.e. in the
left column)

Have you tried it as a column field? Then it would list each sales person in
a different column. Leave everything else as it stands.

Go to Pivot table layout and drag it to the column section.

Or copy your table, so you have one showing the Ox/Res analysis and one the
account analysis.


--
RWS


"Teeroi" wrote:

> Thanks again - thats definitely getting closer. As I have it now, it
> sorts by sales person first, then account, then catg, then item - with
> the item also listed in the data field. I created a formula to count
> down the catg row and subtotal the occurences of the individual
> categories.
>
> =COUNTIF(D:D,"Res")
>
> The only problem is, you have to manually hide the rest of the sales
> people if you want to see the totals for an individual salesperson. Is
> there a way count down from the first sales persons name, but only
> until the formula finds another non-blank cell? Something like:
>
> =COUNTIF(D:D,"Res") & (A:13,"Bob") & (A:A,Non-Blank) ??
>
> Or maybe a way to involve the GetPivotData command?
>
>
> RWS wrote:
> > Ok then lets try this. Its not an ideal solution, but it should give you the
> > answer
> >
> > Open your pivot table wizard and set as follows
> >
> > In column Field put "Sales"
> > In Data field put "item" and ensure field properties are 'count of'
> > In the row field put Acct at the top, Cat next and then item, so you have 3
> > variable in the row field.
> >
> > Close the wizard and go back to the table
> > This shows all sales by acct/salesman
> > In the Cat and item boxes you can deselect the ones you don't want, e.g.
> > blank catg, and zigs
> > This will leave you only sales of ox/res and not include any zigs
> >
> > Of course this still shows you total sales for each salesman, by account.
> > Essentially from here what you want to know for each salesman is "how many
> > rows(accounts) have a number (one or more sale) in them?
> >
> > If you use this formula below the pivot table in each column, and set the
> > d7:d9 bit to a column of your pivot table (data part only not row/column
> > headers)
> > =ROWS(D7:D9)-COUNTBLANK(D7:D9).Copy for each column
> >
> > (This bit is not so clever i am afraid, because as your table changes you
> > will have to do this change manually each time to ensure it counts all
> > rows,(this will change with acct quantities). Somebody may be able to suggest
> > a neater way of doing this last bit)
> >
> > The number it returns is the number of different accounts that the salesman
> > has sold to, including only those categories and products you selected.
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > RWS
> >
> >
> > "Teeroi" wrote:
> >
> > > 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.
> > > > >
> > > > >
> > >
> > >
>
>