+ Reply to Thread
Results 1 to 3 of 3

Formula to pull city that shows the most times, including a number..

  1. #1
    Registered User
    Join Date
    09-25-2018
    Location
    NJ, United States
    MS-Off Ver
    2016
    Posts
    14

    Formula to pull city that shows the most times, including a number..

    Good afternoon,

    Is there a formula to count the city that shows the most in the below 6 cells?
    I was going through countifs and everything, but it seems I will need something more complicated...

    I'm looking for the next cell to basically result in "Sacramento" here since they show 4 out of 6 times.
    Sacramento 1
    Sacramento 0
    Sacramento 1.6
    Washington 0.6
    Washington 1.6
    Sacramento 5.7

    Also, would it be possible to factor in the average and result in Sacramento 2.075 ?

    Thanks for the help!!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,472

    Re: Formula to pull city that shows the most times, including a number..

    Maybe try a Pivot Table.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-11-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 365
    Posts
    66

    Re: Formula to pull city that shows the most times, including a number..

    A pivot table is almost certainly the best solution but if you wanted to do it with formula I think it could be done.

    Assuming that the city (column A) and the number (column B) are in different columns, you could find the most common city then use averageif to get the average for that city. This will probably break if there is more than one city which occurs the same number of times, regardless

    =INDEX(A1:A7,MODE(MATCH(A1:A7,A1:A7,0)))

    This will find the most commonly occurring item in the range A1:A7

    Then just use =AVERAGEIF(A1:A7,A8,B1:B7) to average the corresponding values

    But pivot table is still the best way I think.

+ 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] formula for counting number of times a date shows in a cell that contains other info
    By Moffa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2018, 02:38 AM
  2. Replies: 3
    Last Post: 05-20-2015, 03:04 PM
  3. Percentage of times a certain number shows up in a selection of cells
    By SereneTK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-27-2014, 11:21 AM
  4. Determining how many times a number shows up in a particular range.
    By apreston in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2012, 03:36 PM
  5. Formula to show how many times a name shows up
    By Ajh in forum Excel General
    Replies: 1
    Last Post: 11-09-2010, 04:06 PM
  6. keeping track of how many times a number shows up
    By rismr in forum Excel General
    Replies: 5
    Last Post: 04-22-2009, 04:14 PM
  7. How many times each number shows up?
    By jasoneggleston in forum Excel General
    Replies: 3
    Last Post: 02-23-2008, 04:00 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