Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate PivotTable Item Totals Based On Cell Contents?
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate PivotTable Item Totals Based On Cell Contents?
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate PivotTable Item Totals Based On Cell Contents?
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate PivotTable Item Totals Based On Cell Contents?
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate PivotTable Item Totals Based On Cell Contents?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I conditionally delete rows based on cell contents? | Excel Worksheet Functions | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Reference Data Range based on cell contents | Charts and Charting in Excel | |||
How easy is it to loop in VBA based on cell contents? | Excel Worksheet Functions |