View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arun
 
Posts: n/a
Default Referencing subtotal numbers

Wow, it works! But tell me, what do those double negative signs do?

"Sloth" wrote:

=C2/SUMPRODUCT(--($A$2:$A$13=A2),$C$2:$C$13)

Copy down and format as a percentage. This assumes "Product #" is in cell
A1. It will look like this..

Product # Salesperson Quanity Percentage
1 Amy 10 25.00%
1 Ron 20 50.00%
1 Jim 10 25.00%
Product # 1 Total 55 100.00%
2 Amy 15 17.65%
2 Ron 15 17.65%
2 Jim 35 41.18%
2 Bill 20 23.53%
Product # 2 Total 85 100.00%
3 Jim 35 63.64%
3 Bill 20 36.36%
Product # 3 Total 55 100.00%

The totals show 100% you will probably want to delete them.

"Arun" wrote:

My table didn't format well after posting. I reformatted it below.

Product # Salesperson Quanity Percentage
1 Amy 10 **
1 Ron 20 **
1 Jim 10 **
Product # 1 Total 55
2 Amy 15 **
2 Ron 15 **
2 Jim 35 **
2 Bill 20 **
Product # 2 Total 85
3 Jim 35 **
3 Bill 20 **
Product # 3 Total 55