# 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   Register To Reply

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.  Register To Reply

3. ## Re: Countif cell has higher value than adjoining cells

Thanks hrlngrv,
That's perfect, and I can understand how it works now too   Register To Reply