Question

DougK2016 on Thu, 06 Oct 2016 16:03:50


How should this be written as a result of a recent change I made?  We placed a macro in the workbook to automatically delete blank rows, but it messes up the formula I made in cell I12 copied down through I36 as seen below.  Can this be written somehow to only reference cells down to just above the blank cell in column B? 

Example - If after blank rows have been deleted there is only data in column B12:B22 then it would see the blank in B23 and only include cells I12:I22 respectively.

=IFERROR(IF(AND(COUNT(G12)=1,OR($G$12*$H$12<0,$G$13*$H$13<0,$G$14*$H$14<0,$G$15*$H$15<0,$G$16*$H$16<0,$G$17*$H$17<0,$G$18*$H$18<0,$G$19*$H$19<0,$G$20*$H$20<0,$G$21*$H$21<0,$G$22*$H$22<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0,#REF!*#REF!<0)),G12*(1-H12),""),"")


Sponsored



Replies

Asadulla Javed on Thu, 06 Oct 2016 18:20:16


Try Below.

The #REF! error will come if you delete one cell which is directly referred individually. The SUMPRODUCT takes a range of cell as argument and does not mind if few row deleted from that range.

Secondly the formula becomes shorter.

=IFERROR(IF(AND(COUNT(G12)=1,SUMPRODUCT((G12:G22*H12:H22<0)*1)),G12*(1-H12),""),"")

David_JunFeng on Fri, 07 Oct 2016 06:37:15


Hi DougK2016,

This is the forum to discuss questions and feedback for Excel for Developers, I'll move your question to the Technet forum for Excel IT Pro Discussions

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, 
and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

Thanks for your understanding.

Emi Zhang on Mon, 10 Oct 2016 05:54:00


Hi, 

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

DougK2016 on Tue, 18 Oct 2016 18:45:25


Sorry for my late response.  This works great accept for an important part. 

If there is a single row (G:H) that does fit the And() parameters of column G count =1 and any of G*H <0 (meaning it must be a negative number), then it should show all rows G*(1-H).  The Sumproduct function is not allowing a return value for all rows.  SumProduct only shows a value for the row that has a value in column H.


DougK2016 on Thu, 27 Oct 2016 21:54:14


Please help.

DougK2016 on Wed, 16 Nov 2016 22:26:25


I there another way of doing this and use the prior code?  As mentioned in my last response, there is an issue that I can't resolve. Thank you so much!

DougK2016 on Thu, 01 Dec 2016 18:59:11


I changed something else that resolve this. Thank you for your time. Thanks!