# Countif cell has higher value than adjoining cells

1. ## 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

2. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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