How to exclude cells that contains formula from the range of cells in a LOOKUP function

[MrNobody]

Hello,

I am currently preparing a payment milestone template which also records the last/latest date of payment. I used the this formula to do just that, =LOOKUP (2,1/ (J4:LN4<>"",$J$2:$LN$2)). But the problem starts when I added columns to get the SUM for each activity per month. I wanted to know if there is a condition that I can use to exclude cells with a formula on them. In the first activity "G2-1090", I have deleted the SUM formula at the end of each month and the Last Paid Date column shows the last date of payment which is March 4. But since I wanted to get the sum per month I added a formula at the end of each month to calculate the total payment per activity per month. I have attached the template for reference. I hope someone could give me an advice/solution in this problem. Thank you

[#2]

@MrNobody 

Hi!

Maybe I can help you, but I need to understand your problem better. 

If you want to calculate intermediate sums whose results should not be caught in the final sum, you can try the SUBTOTAL formula.

Use the SUBTOTAL at the end of each month, and then use SUBTOTAL at the end of the year or the quarter. In the last SUBTOTAL formula, even if you include the cells that have the subtotals for each month, those will be ignored and not included in the final sum. 

The SUBTOTAL formula allows you to do other things different than SUM. To do sum, the first parameter is 9.

=SUBTOTAL(9,G5:I5) calculates the sum of cells G5 to G15.

I am not sure if this is what you are looking for. Please let me know how it goes. Good luck!

[#3]

@Celia_Alves 

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.  

[#4]

@MrNobody 

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.

[#5]

@Celia_Alves

 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 

[#6]

@MrNobody 

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.

[#7]

@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),"")

[#8]

Nice one, @Sergei Baklan! As usual. ;)

[#9]

@Celia_Alves , thank you

[#10]

@Sergei Baklan @Celia_Alves 

Thank you guys for the big help means a lot

[#11]

You're welcome! Good luck with your projects.