Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

excel formula Countif and 2 largest numbers in a row


Help Please on a formula that would count the 2 largest numbers in a row greater than 14?

Thank you.




Do you mean count them (2) or report the two largest values over 14?

Also. please modify your Profile to show which bersion of Excel you use.(Answers may vary with version)
John_Ru (rep: 2867) Dec 2, '21 at 11:30 am
Hi John,
I would like to count them(2).  
Thank you,
bmcmay (rep: 2) Dec 2, '21 at 11:47 am
Add to Discussion


Selected Answer


If you only want to count the two largest values in a range (C3:J3) if they are greater than 14 (so can only have answers 0, 1 or 2), this formula should work in all versions of Excel and isn't an array formula:

=IF(LARGE(C3:J3,1)>14,1,0) + IF(LARGE(C3:J3,2)>14,1,0)
In the first haf of the formula, you get a count of 1 if the 1st largest value exceed 14. That's added to the second half which does the same for the 2nd highest value.

Look in the Excel Tutorials section for more on the LARGE function.

Hope this is okay for you.



Perfect  Thank you so much !!
bmcmay (rep: 2) Dec 3, '21 at 4:51 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login