Forum Discussion
How to exclude cells that contains formula from the range of cells in a LOOKUP function
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
10 Replies
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!
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.
