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