Count the last 40 results with criteria in a database-CodePudding

I would like to use countif to count the last 40 results in a database.The column that interests me is E:E ("Confrontos.Casa v Visitante"). At the moment I am using the formula:

=(COUNTIF(Database!E:E;""&[@[Jogador Casa]]&""&""&[@[Jogador Fora]]&""))

However it takes all the data from "Jogador Casa" and "Jogador Fora" what I would like to do is to take the last 30 players results.

I have tried everything and still can't solve my problem.

CodePudding user response：

If I have understood correctly, this should work as you want:

=((ROW()-1) 4)>(COUNTA(A:A)-1) (and you can change the 4 to whatever number you want e.g. 40 for the last 40 players/rows).

The (COUNTA(A:A)-1) part counts all the rows of data (minus one to not count the header). This currently evaluates to 9 (10 rows of data in total minus 1).

The ((ROW()-1) 4) part returns the row number of the cell e.g. in D4 it would return 4. One is also subtracted from this number to not count the header. The addition at the end is what number of last players you want (you suggest 40 but I've just demonstrated with 4).

Putting it all together: for D2, it is the 2nd row, minus one gives 1 and then add four gives 5. As 5 is not > 9, it returns as false (the player/row is not in the last four of the list).

For D7, it is the 7th row, minus one gives 6 and add four gives 10. As 10 is > 9, it returns as true (this player/row is one of the last four of the list).

If this is not what you want then please edit your original post to explain your request further.

CodePudding user response：

Under O365 you can use the following formula in cell D1:

=LET(set, FILTER(A:B, A:A="A"), lastN, 2, counts, ROWS(set),
 skip, counts-lastN, DROP(set, skip))

and here is the output:

In the sample we are considering the last 2 rows (lastN), but you can use a value of your preference (less than counts).

The conditions are stablished via FILTER. We considered one condition, but you can consider more multiplying them in the second input argument. It would represent AND conditions. For example:

FILTER(input, cond1 * cond2...)

where cond1, cond2,.. should be an array of the same number of rows as input and have TRUE,FALSE values.