SUMPRODUCT exclude zero

[mole999]

I have this, (=SUMPRODUCT(MAX((Q14:Q45<>"")*(ROW(Q14:Q45)))) and it works fine except when I need to add a cell marker and this results in 0:00 in the cell, can they be excluded somewhere in this?

 

[Aladin Akyurek]

What do you mean by "a cell marker"?

Why not use a more appropriate formula? Control+shift+enter:

=MAX(IF(Q14:Q45<>"",ROW(Q14:Q45)))

 

[mole999]

I was using a dot in a rota table for when a week is empty from work, but being part of a longer rota. I.e. 6 weeks long, but the sixth has no hours, need to identify the last physical week, and had to show the last week somehow, which was producing 0:00 for hours against the dot