Conversation Re: How to exclude cells that contains formula from the range of cells in a LOOKUP function in Excel
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/525045#M30249
You're welcome! Good luck with your projects.Fri, 03 May 2019 12:17:21 GMTCelia_Alves2019-05-03T12:17:21ZHow to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/501992#M29972
<P>Hello,</P><P> </P><P>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</P>Tue, 30 Apr 2019 02:05:36 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/501992#M29972MrNobody2019-04-30T02:05:36ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502114#M29976
<P><LI-USER uid="330179"></LI-USER> </P><P>Hi!</P><P>Maybe I can help you, but I need to understand your problem better. </P><P>If you want to calculate intermediate sums whose results should not be caught in the final sum, you can try the SUBTOTAL formula.</P><P>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. </P><P> </P><P>The SUBTOTAL formula allows you to do other things different than SUM. To do sum, the first parameter is 9.</P><P>=SUBTOTAL(9,G5:I5) calculates the sum of cells G5 to G15.</P><P> </P><P>I am not sure if this is what you are looking for. Please let me know how it goes. Good luck!</P><P> </P><P> </P><P> </P>Tue, 30 Apr 2019 03:07:12 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502114#M29976Celia_Alves2019-04-30T03:07:12ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502119#M29977
<P><LI-USER uid="302344"></LI-USER> </P><P> </P><P>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. </P>Tue, 30 Apr 2019 03:22:43 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502119#M29977MrNobody2019-04-30T03:22:43ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502153#M29981
<P><LI-USER uid="330179"></LI-USER> </P><P>Try the following: in G5 enter the formula:</P><P>=MAX((ISNUMBER(J4:LN4))*(J2:LN2))</P><P>Instead of hitting enter, hit CTRL+SHFT+ENTER.</P><P>you should get curly brackets at the beginning and the end of the formula like in the picture.</P><P>remember, if you come back to the cell to edit this formula, you always need to hit those 3 keys.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="MrNobody.JPG" style="width: 770px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/111059i9D3B2DB3961DBA78/image-size/large?v=1.0&px=999" title="MrNobody.JPG" alt="MrNobody.JPG" /></span></P><P> </P><P>Let me know if this solves your problem.</P><P> </P>Tue, 30 Apr 2019 03:42:26 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502153#M29981Celia_Alves2019-04-30T03:42:26ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502215#M29984
<P><LI-USER uid="302344"></LI-USER></P><P> </P><P> This is great stuff, finally it works !!</P><P>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.</P><P> </P><P>Thank you again </P>Tue, 30 Apr 2019 04:35:38 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/502215#M29984MrNobody2019-04-30T04:35:38ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/505199#M30038
<P><LI-USER uid="330179"></LI-USER> </P><P>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:</P><P>=IF(SUM(J4:LN4)=0,"",MAX(((J4:LN4>0))*($J$2:$LN$2))) and hit C+S+E.</P><P> </P><P>Or, if you need to write on row 2 in the cells of columns with the subtotals, you can use the following:</P><P>=IF(SUM(J4:LN4)=0,"",<BR />MAX(<BR />NOT(ISFORMULA(J4:LN4))*<BR />(J4:LN4>0)<BR />*($J$2:$LN$2)<BR />)<BR />)</P><P>and hit C+S+E.</P><P> </P><P>I believe this works. Please let me know.</P>Tue, 30 Apr 2019 17:48:34 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/505199#M30038Celia_Alves2019-04-30T17:48:34ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/505417#M30041
<P><LI-USER uid="330179"></LI-USER> , as variant you may expand your LOOKUP with one more filter</P>
<PRE>=IFERROR(LOOKUP(2,1/(J4:LN4<>"")/(($J$1:$LN$1="")),$J$2:$LN$2),"")</PRE>Tue, 30 Apr 2019 18:16:34 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/505417#M30041Sergei Baklan2019-04-30T18:16:34ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/507522#M30070
Nice one, <LI-USER uid="521" login="Sergei Baklan"></LI-USER>! As usual. ;-)Wed, 01 May 2019 11:51:04 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/507522#M30070Celia_Alves2019-05-01T11:51:04ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/507558#M30074
<P><LI-USER uid="302344"></LI-USER> , thank you</P>Wed, 01 May 2019 12:11:27 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/507558#M30074Sergei Baklan2019-05-01T12:11:27ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/522540#M30203
<P><LI-USER uid="521"></LI-USER> <LI-USER uid="302344"></LI-USER> </P><P> </P><P>Thank you guys for the big help means a lot</P>Fri, 03 May 2019 00:22:36 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/522540#M30203MrNobody2019-05-03T00:22:36ZRe: How to exclude cells that contains formula from the range of cells in a LOOKUP function
https://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/525045#M30249
You're welcome! Good luck with your projects.Fri, 03 May 2019 12:17:21 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-exclude-cells-that-contains-formula-from-the-range-of/m-p/525045#M30249Celia_Alves2019-05-03T12:17:21Z