Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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
  •