CODEWITHSUNDEEP
excel-formula
Lorant Sz
Lorant Sz

Reputation: 13

Count last 10 cell values if >1, exclude blank cells

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. The formula should handle last 10 non-blank entries dynamically in a column.

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.

Upvotes: 1

Views: 2095

Answers (2)

Boris Stitnicky
Boris Stitnicky

Reputation: 12578

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...

Upvotes: 0

Mrig
Mrig

Reputation: 11712

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.

Upvotes: -1

Related Questions