try formula please find attachment........... On Thu, Aug 25, 2011 at 8:04 PM, 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 > -- ......................... -- ---------------------------------------------------------------------------------- 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
multipal sum.xls
Description: MS-Excel spreadsheet
