I am trying to figure out how to count the last 10 cell values that are bigger than 1, in a given range, and to exclude blank cells.
I have this array formula from another forum:
=IFERROR(AVERAGE(IF(ROW(A2:A20)>=LARGE(IF(A2:A20<>"",ROW(A2:A20)),MIN(COUNT(A2:A20),10)),IF(A2:A20<>"",A2:A20))),"")
It averages the last 10 cell values, excluding blanks.
I have tried to replace the "average" function with "countif", and I added ">1", but without success,it returned zero.
The problem with your question is that you only need last 10 cells of your range. Assuming your range is A2:A20, you count the last 10 values greater than 1 as follows:
=COUNTIF(OFFSET(A2,ROWS(A2:A20)-10,0,10,1),">1")
Note that you have to type in the first cell of the range (A2) as well as the whole range (A2:A2O) and you have to type number 10 twice, but that's how Excel programming is...
Made some changes in your formula:
=IFERROR(SUM(IF(INT(IF(ROW(A2:A20)>=LARGE(IF(A2:A20<>"",ROW(A2:A20)),MIN(COUNT(A2:A20),10)),IF(A2:A20<>"",A2:A20)))>1,1,0)),"")
This is an array formula so commit it by pressing Ctrl+Shift+Enter.
Instead of COUNTIF function, I am using SUM(IF()). For deatils see this.
Here, INT function converts False to 0 and TRUEto 1.
