+ Reply to Thread
Results 1 to 11 of 11

Count category as per highest number count

  1. #1
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Count category as per highest number count

    Hi all
    I Need a help in my data analysis part .in current situation I am expecting the category name in the attached table should update automatically as per below conditions.
    In the table I want to count the category, listed in A1:A50
    The only condition is that, it should be captured in ascending order in terms of highest number count in “J”
    And whenever there is any change in category, listed in A1:A50 the table should update accordingly.
    Regards
    Arindam

    cat.xls

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Count category as per highest number count

    Try with some helper columns

    F3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    drag down till F13

    G3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    drag down till G13

    I3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    drag down

    J3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    drag down.

    Now you can hide column no F & G .

    It will auto update and efficient in large data set.

    Check the attached file.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count category as per highest number count

    I3
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Count category as per highest number count

    Hi Both
    Thanks for you response.
    I have tried both the formula in my file.
    There is some issue
    1) In shukla.ankur's Formula.the Category's r reflecting multiple times. Please check in the below attached file

    2) In Nflsales's Formula ,In My file it is not reflect any data I might going wrong in the formula,so request you to cross check in the bellow attached file.

    Attached File
    1cat.xls

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count category as per highest number count

    See the attached file you have blank cells in the range that created the problem see the attached file
    CL3=
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Count category as per highest number count

    @nflsales
    Thanks for your continuous help.
    It's working as per my requirement.
    Now for one of the file I need a slight change with the same formula.
    in this file I have 3 different table with the same formula,Condition is
    1) if BU "Category"="Cancelled by Requester " then the "BV""Sub Issue" should calculate in table -1 which is in "CL",
    2) if BU "Category"="Unable to Progress " then the "BV""Sub Issue" should calculate in table -2 which is in "CN"
    3) if BU "Category"="Duplicate" then the "BV""Sub Issue" should calculate in table -3 which is in "CP"

    Apart from this if you check "BV" I have selected 3 different Sub issue
    1) Agreement Information Incomplete/Incorrect/Invalid*
    2) Mismatch between drafting information provided
    3) Approvals Incomplete/Inaccurate/Missing**
    as per your previous formula only 2 Subissue is getting captured in each table,if there is any changes required in you previous formula to avoid this please do let me know.Then I have another file wher I need to change the previous formula.

    Sorry for the inconvenience,I need your kind support to resolve this.
    Thanks in advance.

    Regards
    Arindam

    1cat.xls

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count category as per highest number count

    why can't you use Pivot Tables, it will be handy and can save much time, if you go for formula your system will be slow while performing calucations
    see the attached file performed your expected result with Pivot Table
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Count category as per highest number count

    Hi nflsales
    Pivot Tables will not work on this as the data will vary in case by case.
    so I need the same thing as Pivot Tables by formula.if possible please let me know.

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count category as per highest number count

    it is possible

  10. #10
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Count category as per highest number count

    Please send me the file with the formula,it will save a huge time for me.

    Thanks in advance.

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count category as per highest number count

    CL3
    Please Login or Register  to view this content.
    Try this and copy across
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] vLookup to Count Number of Completed Category Items for Report
    By rstarr1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2015, 09:52 AM
  2. Count number of value that belongs to a specific category
    By geoff0409 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2015, 02:56 AM
  3. Replies: 9
    Last Post: 04-27-2015, 06:50 AM
  4. [SOLVED] Count highest number of letter in a cell
    By puuts in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2014, 12:41 AM
  5. Count number of times when data is highest among a column
    By arrontan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-20-2013, 06:10 AM
  6. Return number with highest frequency count
    By Archibald_SM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2011, 06:14 PM
  7. Replies: 9
    Last Post: 02-24-2010, 07:20 AM

Tags for this Thread

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