+ Reply to Thread
Results 1 to 3 of 3

Countif cell has higher value than adjoining cells

  1. #1
    Registered User
    Join Date
    10-13-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    2

    Countif cell has higher value than adjoining cells

    Hello everyone,
    I'm only a novice at excel, so forgive me if this is a simple one, but I have been unable to find a formula that works for this calculation.
    I'm looking for a formula that counts the number of times the value in a column is the lowest/highest when compared to adjoining cells for a given criteria.
    So in the example attached, I'm looking to count how many times each person had the best score in January.
    I've tried a few different COUNTIFS and SUMIF but can't come up with one that works. Thanks for your help
    Attached Files Attached Files
    Last edited by BrettEng; 10-17-2020 at 11:10 PM.

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    871

    Re: Countif cell has higher value than adjoining cells

    The only straightforward way to do this in Excel requires adding columns to the table in A2:F15. Namely,

    G4: =MAX(C4:F4)
    H4: =MIN(C4:F4)

    Select G4:H4 and fill down into G5:H15. Then,

    C19: =SUMPRODUCT(($B$4:$B$15=$A19)*(C$4:C$15=$G$4:$G$15))

    C24: =SUMPRODUCT(($B$4:$B$15=$A24)*(C$4:C$15=$H$4:$H$15))

    Fill C19 right into D19:F19, and fill C24 right into D24:F24. You can them fill C19:F19 and C24:F24, respectively, down as far as needed.

  3. #3
    Registered User
    Join Date
    10-13-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Countif cell has higher value than adjoining cells

    Thanks hrlngrv,
    That's perfect, and I can understand how it works now too
    Really appreciate your response.

+ 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. Replies: 5
    Last Post: 06-18-2019, 02:49 AM
  2. count cells that have a higher value than the cell above it in a row
    By umavaidya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2016, 03:02 AM
  3. Replies: 4
    Last Post: 03-09-2016, 11:03 AM
  4. Bonding Adjoining Cells
    By db1974 in forum Excel General
    Replies: 2
    Last Post: 12-16-2014, 06:14 PM
  5. Replies: 1
    Last Post: 01-06-2014, 09:39 PM
  6. Replies: 2
    Last Post: 05-31-2010, 03:23 PM
  7. [SOLVED] Search range of cells, find a value, output adjoining cell. How?
    By nyys in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-05-2006, 09:55 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