+ Reply to Thread
Results 1 to 5 of 5

Finding the max occurence with the INDEX, MATCH and MAX formula

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Finding the max occurence with the INDEX, MATCH and MAX formula

    I have the following countif function to count the number of black hondas

    =COUNTIFS(table1!A:A,"Honda",table1!B:B,"Black")

    and the following to get which Honda is more: black or white

    = MAX(COUNTIFS(table1!A:A,"Honda",table1!B:B,"Black"),COUNTIFS(table1!A:A,"Honda",table1!B:B,"White"))

    But I want to also display "Honda" next to the value and after I search I composed the following formula

    =INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0))

    but I can't get it to work when combined with the previous two.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Finding the max occurence with the INDEX, MATCH and MAX formula

    Not clear to me what is in A1:a7- can you post a small file show required results.

  3. #3
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Re: Finding the max occurence with the INDEX, MATCH and MAX formula

    The A1:A7 is just an example. I'm not sure if in my case it's only table!B:B, "Black" or (table1!A:A,"Honda",table1!B:B,"Black")

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Finding the max occurence with the INDEX, MATCH and MAX formula

    It is the ' "Honda" next to the value ' I don't understand: a sample file (hopefully) will make this clear

  5. #5
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Re: Finding the max occurence with the INDEX, MATCH and MAX formula

    I can get the value of the color that has maximum occurrences, say 12. But next to it, I want to display 'Black' so that people know that there are a 12 black hondas.

+ 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. finding partial text in an index match formula
    By garyaw in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 09-30-2015, 04:27 PM
  2. [SOLVED] Finding second to last value using vlookup match or index match...
    By gr8spot in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-20-2015, 03:32 PM
  3. Replies: 6
    Last Post: 03-06-2015, 03:20 PM
  4. Using Index and match to find last occurence
    By morbdetro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2014, 01:25 PM
  5. [SOLVED] Index And Match to find second occurence Help wanted
    By tropsog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:24 PM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. Multiple value occurence index/match
    By mechen8 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2012, 07:01 AM

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