HELP!!!! I need to sum and get averages of data with varying

wi_guy · Mar 22nd, 2002, 02:23 PM

The data may look like this:

SKU Region #s sold

100 1 1000
100 2 5000
100 3 4000
101 1 3000
102 2 500
102 5 500
103 1 1000
103 2 1000
103 4 3000

I need it to calculate the pct of total for each SKU.....first I can subtotal by SKU to get the totals, but then I'd have to manually add a column of formulas to calculate %

Final data would look like this

SKU Region #s sold Pct of SKU

100 1 1000 10%
100 2 5000 50%
100 3 4000 40%
101 1 3000 100%
102 2 500 50%
102 5 500 50%
103 1 1000 20%
103 2 1000 20%
103 4 3000 60%

Mark W. · Mar 22nd, 2002, 02:33 PM

With your data in A1:C10 enter the formula, =C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10), into D2 and copy down to D10. Format D2:D10 as 0%

wi_guy · Mar 22nd, 2002, 02:49 PM

THANKS A LOT MARK, WORKED LIKE A CHARM!!!!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 22nd, 2002, 02:23 PM	#1
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	The data may look like this: SKU Region #s sold 100 1 1000 100 2 5000 100 3 4000 101 1 3000 102 2 500 102 5 500 103 1 1000 103 2 1000 103 4 3000 I need it to calculate the pct of total for each SKU.....first I can subtotal by SKU to get the totals, but then I'd have to manually add a column of formulas to calculate % Final data would look like this SKU Region #s sold Pct of SKU 100 1 1000 10% 100 2 5000 50% 100 3 4000 40% 101 1 3000 100% 102 2 500 50% 102 5 500 50% 103 1 1000 20% 103 2 1000 20% 103 4 3000 60%

Mar 22nd, 2002, 02:33 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	With your data in A1:C10 enter the formula, =C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10), into D2 and copy down to D10. Format D2:D10 as 0%

Mar 22nd, 2002, 02:49 PM	#3
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	THANKS A LOT MARK, WORKED LIKE A CHARM!!!!!