+ Reply to Thread
Results 1 to 6 of 6

Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

  1. #1
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Excel 2007
    Posts
    154

    Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

    I am wanting to list in Columns S, U and X the types of Defects of the fruit from most frequent to least frequent. Though, my attachment does not show it, I figured out how to display the frequency using the formula below for Columns T, V and Y.

    =SUMPRODUCT((F2:F13=S1)*(G2:G13=S4))

    S4 is referring to a value of "Taste" I entered in which gave the correct frequency of that particular defect of the fruit. The file is attached to this thread post. How would I go about finding and displaying the unique category "Defects" for a particular main category such as Apricots from most frequent to least frequent (listing each defect only once)?

    An example would be, that the greatest defect is Insects. Insects woul be listed first with the number frequency of Insects defects displayed in the column to the right of this. Insects would not be a repeated category since it is already displayed.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,779

    Re: Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

    I seem to recall helping you with an earlier version of this

    try the attached and see if we are headed in the right direction?
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Excel 2007
    Posts
    154

    Re: Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

    Err this is a tad different in what I am looking for. Under my original daddy_fruits_V2(1), Under Apricots (Column S) I want to display the name of the highest frequency to lowest frequency of defects (i.e. Insects, Bruised, etc.) In column T, I want to display the frequency of those defects. The order in which each defect is listed for each fruit will be different according to its frequency of defects. But the defects will always be listed from highest to lowest.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,779

    Re: Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

    ok, try this then, i think this is what you want

    edit:

    i guess it would help if i actually attached the file??
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Excel 2007
    Posts
    154

    Re: Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

    Hmm, it's close to what I want. Another constraint would be to avoid "breaking out" the tally of defects shown from $R$18:$AC$25. I was trying to play around with the code:

    Please Login or Register  to view this content.
    found in $N2:$N12. The challenge now is summing based on defect based on a specific fruit. I am trying to avoid having to display repetitive data ($R$18:$AC$25). Basically I want to show:
    • Fruits from highest to lowest quantity in a formula that uses the original data($N2:$N12, Unique)
    • Top three largest quantity fruits listed next to the unique category of fruits that display their defects from largest recurrence to the least utilizing a formula that uses the original data ($D2:$H13)

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,779

    Re: Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category

    my problem with trying to give you what you need, was trying to find the fruit name, and then trying, based on that name, to find the sum the values and then trying to identify which was the 1st largest, 2nd etc, and then to rank all of that. it became so involved and confusing that it turned out easier and cleaner to just break it out and do it in a 2-step process

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1