Forum Discussion
How to exclude cells that contains formula from the range of cells in a LOOKUP function
Thank you for your reply and your suggestion of using SUBTOTAL will definitely be added to the template. My aim is for the "Last Paid Date - Column G" to return the last date of payment for the activity from the months to the right i.e. when there are 2 or 3 payment dates for the activity, it will return the last date only. LOOKUP function seems do this at first but when I added the SUM column per month, it doesn't work anymore I guess because in the SUM column there is no dates.
Try the following: in G5 enter the formula:
=MAX((ISNUMBER(J4:LN4))*(J2:LN2))
Instead of hitting enter, hit CTRL+SHFT+ENTER.
you should get curly brackets at the beginning and the end of the formula like in the picture.
remember, if you come back to the cell to edit this formula, you always need to hit those 3 keys.
Let me know if this solves your problem.
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
MrNobody , as variant you may expand your LOOKUP with one more filter
=IFERROR(LOOKUP(2,1/(J4:LN4<>"")/(($J$1:$LN$1="")),$J$2:$LN$2),"")
- Nice one, SergeiBaklan! As usual. ;-)
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.
