Count the number of column that the last value is greater than last three rows...

[jackyuen]

I have a data set (a1:j4) as below...
TO Count the number of column that the last row value is greater than last three rows...
i try a formula but its too long...can anyone simpify it
thanks

3 2 3 3 1 1 2 2 2 1
1 1 1 1 1 1 2 3 1 3
2 1 2 2 3 1 2 2 3 2
4 4 5 3 1 2 4 2 4 1

the formula of K4= SUM(A4>MAX(A1:A3),B4>MAX(B1:B3),C4>MAX(C1:C3),........,J4>MAX(J1:J3))
(OUTPUT = 6)

[6StringJazzer]

How are you getting 6? I get 5.

Try this formula. It is an array formula, so you need to enter it by hitting CTRL-SHIFT-ENTER (not just ENTER).

Please Login or Register  to view this content.

If you do that properly, you will see it in the formula bar with brackets, but you can't just type the brackets it.

Please Login or Register  to view this content.

[jackyuen]

Excellent....thats what i want...thank you

^_^

[jackyuen]

How are you getting 6? I get 5.

Try this formula. It is an array formula, so you need to enter it by hitting CTRL-SHIFT-ENTER (not just ENTER).

Please Login or Register  to view this content.

If you do that properly, you will see it in the formula bar with brackets, but you can't just type the brackets it.

Please Login or Register  to view this content.

So strange you got 5? I overlook into your answer...
Column A , B, C ,F ,G ,I ...The last row of these 6 columns is greater than row 1 to row 3 ...by inspection only...

[Debraj Roy]

Hi jackyuen !

Can you please attached a sample file..

I mean.. If I enter.. 9 in between any cell A1:J4, I got answer as 0.. as None of the last ROW in any column is greater than 9,,

@6StringJazzer
isn't is checking if any cell is greater than Just the MAX of A1:J3, In sample file..3
Or I am over-reacting..

[Haseeb Avarakkan]

May be this,

=SUMPRODUCT((A4:J4>SUBTOTAL(4,OFFSET(A1:A3,,COLUMN(A4:J4)-COLUMN(A4))))+0)

[jackyuen]

thank you Haseeb...
exactly what i want....!!!

[6StringJazzer]

My apologies for the error I'm glad you got your answer