Need formula for position of Last Row in a Range

[jbesclapez]

Hello Mr Excel!

I have a formula that looks like the one I need, but it only does the job for one Column at a time and not a multi column Range.
=+LOOKUP(2,1/(B4:B10<>""),ROW(B4:B10))
From that i get the position of the last non empty member
I would like to get the same but instead of B4:B10 it should be B4:Z10 ...

Can you please do your magic MrExcel?

Thanks.

 

[Anthony47]

For example

=MAX(IF(B4:Z10<>"",ROW(B4:B10),""))

It has to be confirmed using Contr-Shift-Enter, not only Enter

Bye

 

[jbesclapez]

For example

=MAX(IF(B4:Z10<>"",ROW(B4:B10),""))

It has to be confirmed using Contr-Shift-Enter, not only Enter

Bye

Thanks Anthony! Really appreciated.... BUT... (the famous "but" that means trouble ... could we do this without array formulae?
I am sorry for not mentioning that earlier in my requirements. I totally forgot about this!
Thanks

 

[mart37]

=SUMPRODUCT(LARGE((B4:Z10<>"")*ROW(B4:Z10),1))
or
=SUMPRODUCT(MAX((B4:Z10<>"")*ROW(B4:Z10)))

 

[jbesclapez]

=SUMPRODUCT(LARGE((B4:Z10<>"")*ROW(B4:Z10),1))
or
=SUMPRODUCT(MAX((B4:Z10<>"")*ROW(B4:Z10)))

Thanks Mart37 it is exactly that! Have you got any idea on which of those 2 formulae is the fastest?

 

[Anthony47]

Thanks Anthony! Really appreciated.... BUT...

Just a curiosity, why array formulas are so bad to avoid them?
You got from MART the Sumproduct solution; it works because Sumprodct is intrinsically an array formula

Bye

 

[jbesclapez]

Just a curiosity, why array formulas are so bad to avoid them?
You got from MART the Sumproduct solution; it works because Sumprodct is intrinsically an array formula

Bye

It is because I am building this in excel, but ultimately it will be in a "look alike" software like spreadsheet...