If your data is in A1:D10, maybe:
=filter(A1:D10,A1:A10>500)
To allow for "new ones will be added" put the formula outside A:D and adjust to:
=filter(A1:D,A1:A>500)
Edit to include comment from someone who knows (@AdamL):
it is not possible to do what you want with the Sheets filter tool alone. You can combine both the filter tool and a FILTER or QUERY formula (eg apply the filter tool to the output of pnuts' solution, which could also reference the 500 part in another cell, perhaps in the header row to the side). Alternatively, a helper column(s) could be used to simulate the required functionality.
In the cell where you want to total, place this for the Kinder example:
=SUMIF(A11:A, "=Kinder", B11:B)
Since your grades are very limited, you could easily edit this for each case. Another option would be to reference the cell where Grade is defined:
=SUMIF($A$11:$A, "=" & D1, $B$11:$B)
In this example, you would place the formula in cell E1 and have Kinder in D1. You could then copy the formula down to other rows (E2, E3, etc) and have 1st, 2nd, etc in cells D2, D3, etc.
The $ in the Range tells the program to not increment that item when copying or moving the formula.
Best Answer
Using the
=filter
function, you can try this :Note : Depending on your language settings, you can replace
;
with,