# Thread: Obtaining the % of total for a list of items

1. ## Obtaining the % of total for a list of items

In the attached worksheet I have a yellow highlighted the examples of the goal for the first two locations. What I would hope for is a formula(s)to calculate the percentage fo each TYPE within each LOCATION to copy to automate this schedule. It is quite a lengthy report.

Thank you

2. Not sure I understand exactly, but are looking for:
=C2/SUMIF(\$B\$2:\$B\$92,B2,\$C\$2:\$C\$92)

[Copy down the column]. It is amount of C2 in all the realtors.

Steve

3. If you are looking at the value over the sum of the total location you can use:
=C2/SUMIF(\$A\$2:\$A\$92,A2,\$C\$2:\$C\$92)*2

The "*2" comees because the sum of the location is doubled since it has the individual values and the total.

Steve

4. MNN,

Taking a stab at interpreting what you want to achieve. It seems as though you are looking for a way to set up your sheet automatically to include all the formulas to calculate your percentages. Here is some code that will generate and place those formulas. The images below show the sheet before running the code having no formulas and an image after running the code that has the formulas placed and values calculated.

HTH,
Maud

Code:
```Public Sub BuildFormulas()
'SET VARIABLES
LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Total = 2
Row = 2
For I = Row To LastRow
'------------------------------
'FIND NEXT TOTAL ROW
For J = Row To LastRow
If Left(Cells(J, 2), 8) = "LOCATION" Then
Total = J
Exit For
End If
Next J
'------------------------------
'CREATE FORMULAS FOR BLOCK
For K = Row To Total
Cells(K, 4).Formula = "=If(OR(C" & Total & " = 0, C" & Total & "= " & Chr(34) & Chr(34) & "), " & _
Chr(34) & Chr(34) & ", C" & K & "/C" & Total & ")"
Row = Total + 1
Next K
I = Total
Next I
End Sub```
Prior to running code
Type1.png

After Running Code
Type2.png

5. I'll give these a spin.

Thanks for all the help

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•