I have solve your worksheet but you must changed the presentation of your data. You must present your data like this Invoice# Year Cat Dollar 123456 2006 Cat1 5 234567 2006 Cat1 5 345678 2006 Cat1 5 123456 2006 Cat2 4 234567 2006 Cat2 4 345678 2006 Cat2 4 123456 2006 Cat3 3 234567 2006 Cat3 3 345678 2006 Cat3 3 987654 2007 Cat1 3 876543 2007 Cat1 3 765432 2007 Cat1 3 987654 2007 Cat2 4 876543 2007 Cat2 4 765432 2007 Cat2 4 987654 2007 Cat3 5 876543 2007 Cat3 5 765432 2007 Cat3 5
And then, you put the Year in column, the Cat in Row and Dollar in the field Sum in your pivot table And you finish by changing the value setting of the Dollar item and you select % of Row total in the tab 'Show values as' On 25 août, 16:34, Robert Jacobs <robertjacob...@gmail.com> wrote: > Thanks in advance experts!!! This is very complicated to explain, but > I'll give it a go: > > I have data (Invoice#, Invoice Year, Cat1 Sales, Cat2 Sales, Cat3 > Sales), and need to find the percent of sales for each category, for > each year in my data. > > For instance, if I have: > > Invoice# Year Cat1 Cat2 Cat3 > 123456 2006 $5.00 $4.00 $3.00 > 234567 2006 $5.00 $4.00 $3.00 > 345678 2006 $5.00 $4.00 $3.00 > 987654 2007 $3.00 $4.00 $5.00 > 876543 2007 $3.00 $4.00 $5.00 > 765432 2007 $3.00 $4.00 $5.00 > > I need a pivot table that will sum all values for each category by > year, then tell me what percent of that year's sales belonged to that > category. I can get a pivot table to show each category by year with > total sales like this: > > Values 2006 2007 > Cat1 $15.00 $9.00 > Cat2 $12.00 $12.00 > Cat3 $9.00 $15.00 > > BUT, I really need the percent of each category for that column. For > instance, 2006 and 2007 both have $36.00 total sales. So I would want > to know what percent of that $36.00 was for Cat1, Cat2, and Cat3. > Something like this: > > Values 2006 2007 > Cat1 42% 25% > Cat2 33% 33% > Cat3 25% 42% > > Does anybody have any recommendations? I have tried Show Value As > Percent of Column, but it always shows 100%. If I Show Value As > Percent of Row, it shows what percentage of the total sales of Cat1 > was sold for each year. i.e. - Cat1 would show 62% for 2006 and 38% > for 2007. Any help would be GREATLY appreciated!!!!!!! Thank you! -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
