Which function do i have to use to show me the total of players-over a number(s)

Hello everyone!

Here is my question and an example.

For example: I have 5 names in a column with the name of the column PLAYERS, also at the second column i have their FOULS.

1st column PLAYERS

David
John
Mark
Brad
Michael

And in the second (fouls)

3
3
3
5
1

The question is: In the cell b12 show the total players which they collected 3 and above fouls

(i tried sumif but couldnt solve this) Any suggestions ?

Thank you in andvanced.

Hello,

Do you want to get the number of players with 3 fouls or above, or do you want a list of the players with 3 fouls or above?

Ferdy

Try the below formula in B12:

=COUNTIF(B1:B5,">="&3)

Originally Posted by FerdyHar

Hello,

Do you want to get the number of players with 3 fouls or above, or do you want a list of the players with 3 fouls or above?

Ferdy

Hello thank you for the quick respond! The second one.

Originally Posted by Glenn Kennedy

=COUNTIF($B$2:$B$7,">=3")

will do that...

Nice, thank you! and also, is there a function that can show the names ?

Originally Posted by cbatrody

Try the below formula in B12:

=COUNTIF(B1:B5,">="&3)

thank you a lot for the answer! Very helpful!

Originally Posted by Kaper

As for names - try such array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Adjust just upper limits ($100 which is used 3 times in the formula, do not change upper limits (1) of ranges, and especially the last reference to cell A1.

THANKS!!! i appreciate it a lot!