Google Sheets FILTER, COUNT, MAX Function | Nest FILTER inside COUNT | Extract Spreadsheet Data

The Google Sheets FILTER function combined with, for example, the MAX and COUNT function, can be used to extract specific data from a spreadsheet. The FILTER function is one of the most powerful and versatile Google Sheets functions.

----------------------------------------------
Here are the links to the FILTER, COUNT, and MAX functions:

FILTER

COUNT

MAX
----------------------------------------------

Here is a scenario when you would want to use, say the FILTER, MAX, and COUNT functions to get the data based on some conditions:

Let's use this spreadsheet data for our task:

ID Name Physics Chemistry Maths Comp. Sc.
30389 John 78 83 84 75
30129 Arun 97 99 100 99
30541 Linda 99 100 100 99
30277 Mary 79 74 81 86
30528 Pradeep 99 95 100 100

Assume that you want to get the highest score, and number of students, with such a score, for example, in Physics. You also want such students ID and name.

1. Get Highest Score Using MAX Function

You can easily get the highest score in Physics, using the MAX function.

Here is the format of the MAX function formula:

=MAX(value1, [value2], […])

value1 is the first range or number. If it is a range, and no other attributes are used, the MAX function formula returns the maximum value in the range.

value2 is optional and is the second range or number. If it is a range, and no other attributes are used, the MAX function formula returns the maximum value in the range.

Say in the spreadsheet physics scores are in the range C5:C9. Let's use this as the value1 attribute in the formula:

=MAX(C5:C9)

The function returns 99.

2. Count Number of Students with Highest Score Nesting FILTER Inside COUNT Function

First, let's use the FILTER function to extract data based on a condition. The data we want to extract is the highest score. The condition is that only the maximum score in the specified spreadsheet range should be considered.

The FILTER function formula is:

=FILTER(range, condition1, [condition2], [...])

range is the spreadsheet range from which we want to extract the data. In this example, the range is C5:C9.

condition1 is the first condition that range should satisfy. The condition in
this project is C5:C9=max(C5:C9). It tells the function to consider only the
maximum value in the specified range. Note that more than one student might have got the highest score.

condition2 and subsequent conditions are optional.

Let's plug in the values for range and condition in the formula:

=FILTER(C5:C9, C5:C9=max(C5:C9))

This function used as a standalone will return:

99
99

Now, let's nest the FILTER function inside COUNT, so we can get the number of students with the highest score.

The COUNT function formula is:

=COUNT(value1, [value2], […])

Since FILTER function returns a value, we can this function as the value1
attribute of COUNT:

=COUNT(FILTER(C5:C9, C5:C9=max(C5:C9)))

The COUNT-FILTER combo returns:

2

That is, two students have obtained the highest score in physics.

3. Get the ID and Name of Students with the Highest Score Using FILTER Function

Let's use the same above FILTER function formula:

=FILTER(C5:C9, C5:C9=max(C5:C9))

Remember, we want to extract the ID and Name data of students with highest score in physics. So, we need to change the range, the first attribute, to cover ID and name.

Assume that ID and Name data is in the range A5:B9. The modified formula is:

=FILTER(A5:B9, C5:C9=max(C5:C9))

The formula returns:

30541 Linda
30528 Pradeep

Take a look at this video tutorial, which gives the steps to use the Google
Sheets, FILTER, MAX, and COUNT function, with an example.