Referencing subtotal numbers

[Guest]

I need to find a way to reference a subtotal relative to one of it's lines.

Example
Product # Salesperson Quanity
Percentage
1 Amy 10
**
1 Ron 20
**
1 Jim 25
**
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

I of course want to calculate the percentage column by dividing the quantity
by the total. The problem is that the relative position of the total varies.
Is there a way to do this without using code?

Thanks,
Arun

 

[Guest]

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

 

[Guest]

Assumes column A has the product # & col B has the Quantity

Also assumes the product # IS a number

=IF(ISNUMBER(A2),C2/SUMIF(A2:A50,A2,C2:C50),"")

 

[Guest]

=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.

 

[Guest]

Didn't quite work. The SUMIF function didn't take into account values above
the line it was working on. The final spreadsheet came out like this.

Product # Salesperson Quanity Percentage
1 Amy 10 25%
1 Ron 20 67%
1 Jim 10 100%
Product # 1 Total 55
2 Amy 15 18%
2 Ron 15 21%
2 Jim 35 64%
2 Bill 20 100%
Product # 2 Total 85
3 Jim 35 64%
3 Bill 20 100%
Product # 3 Total 55

 

[Guest]

My error - should have been

=IF(ISNUMBER(A2),C2/SUMIF(A$2:A$50,A2,C$2:C$50),"")

 

[Guest]

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

 

[Guest]

Also, is there a more elegant way of replacing all the #DIV/0! errors with
zeros than using an IF(....) statement?

 

[Guest]

SUMPRODUCT ignores any nonnumerical value. the -- converts the logical
values (TRUE and FALSE) to ones and zeros.

 

[Guest]

not that I know of. give me an example of a line that gives the error and I
will try and make a small formula for you.

 

[Guest]

No big deal. I just didn't want it to do the SUMPRODUCT search twice in each
cell since there are over a thousand lines of data. For example if product 1
had zeros for all the salespeople, it would have to search through all the
data to first check if it adds to zero, and if not, search through it a
second time to get the value to be used in the formula.

Come to think of it, I could just write IF(C2=0,0,C2/SUMPRODUCT(...)). In
other words, if the value is not zero then the sum must be over non-zero. If
it is zero, then it's zero anyway.

Thanks again for all your help.