# Is there a way to PERCENTRANK or RANK but only count the list values that match a criteria?

0
0
2
0
1
1
43
23
11
8
0
0

1
don’t count if it’s <2

Hallo

``````=IF(A1>1;RANK(A1;IF(A\$1:A\$12>1;A\$1:A\$12;""));"")
``````

I didn’t know that you can compare a range and it will auto-align a pulled out of the range value in a relative way for a current copied cell when copying, without specifying it… Well that’s some weird magic A\$1:A\$12>1.

Even though I was thinking more of a as a single cell without copying.

It’s called implicit intersection. For function parameters that expect a single scalar value as argument if given a cell range single column or row vector, the value of the intersection of that vector and the formula cell position is taken.

I don’t think that it works actually. It just cuts the numbers on the first check “A1>1”, rather than feeding it a range without 0s and 1s, which is what I want. It’s more obvious with PERCENTRANK.

After having entered the formula in B1 you need to pull/copy-paste the formula cell down until the end of the data range, e.g. to B12.

I believe that not the `Value` should be checked for >= 2, but `Data` should be filtered, per OP question.

Something like

``````RANK(A1; A\$1:A\$12 * (A\$1:A\$12 >= 2))
``````

(I can’t test at the moment, and suppose that my formula is incorrect, since it would still count everything below 2 as 0)

you need to check the [x]matrix-option in Formula_wizardAND hold the `<ctrl>` key meanwhile pulling down.