Forum Discussion
How to exclude cells that contains formula from the range of cells in a LOOKUP function
This is great stuff, finally it works !!
The remaining problem is if there is no data in the row, it still returns a date, I hope you can help me solve this one.
Thank you again
In fact, just realized that the ISNUMBER part does not well here. It was meant to disregard the formula cells but does do the intended job. In this case, we don't need it anyway because the columns with formulas are empty in row 2. If you keep that like that, you can use the formula:
=IF(SUM(J4:LN4)=0,"",MAX(((J4:LN4>0))*($J$2:$LN$2))) and hit C+S+E.
Or, if you need to write on row 2 in the cells of columns with the subtotals, you can use the following:
=IF(SUM(J4:LN4)=0,"",
MAX(
NOT(ISFORMULA(J4:LN4))*
(J4:LN4>0)
*($J$2:$LN$2)
)
)
and hit C+S+E.
I believe this works. Please let me know.