LinkBack URL
About LinkBacks
Hi
I'm kindly asking for a formula that if column J in TXs worksheet is 0 (zero) it does not reflect on the Bank worksheet, for example rows 22,23,26 t0 31 should not appear in the bank statement. See the excel sheet.
Thank you.
Regards,
Sunboy
Hi
I'm kindly asking for a formula that if column J in TXs worksheet is 0 (zero) it does not reflect on the Bank worksheet, for example rows 22,23,26 t0 31 should not appear in the bank statement. See the excel sheet.
Thank you.
Regards,
Sunboy
Try this:
=IFERROR(1/(1/IF(TXs!$J6="","",INDEX(TXs!$G$6:$G$1783,AGGREGATE(15,6,ROW(TXs!$G$6:$G1783)/(TXs!$B$6:$B$1783 > =$E$10)/(TXs!$B$6:$B$1783 < EDATE($E$10,1)),ROWS(I$19:I19))-ROW($B$5)))),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi
Are you using excel 2010?
Hi Ali
Thank you for the formula. However the formula is producing blank rows but I want the rows with blank or zero amounts not to appear in the bank statement. I think I was not clear, my apologies. See the desired outcome attached.
Thank you.
Oh, I see - sorry.
Hi
I'm using excel 365
Then please update your forum profile, which tells us you are using Excel 2010.
Hi Ali
I have updated my profile
Hi
If you are using 365 you can use the FILTER function
B19 and across:
=FILTER(FILTER(TXs!$B$6:$J$178,((TXs!$J$6:$J$178 > 0)*(TXs!$B$6:$B$178 > =$E$10)*(TXs!$B$6:$B$178 < EDATE($E$10,1))) > 0),{1,0,0,1,1,1,0,0,1})
And with your original formula:
F19 and down:
=IFERROR(IF(TXs!$J6="","",INDEX(TXs!J$6:J$1783,AGGREGATE(15,6,ROW(TXs!$F$6:$F$1783)/(TXs!$J$6:$J$1783 > 0)*(TXs!$B$6:$B$1783>=$E$10)/(TXs!$B$6:$B$1783 < EDATE($E$10,1)),ROWS(I$19:I19))-ROW($B$5))),"")
Hi Belinda2000
Thank you for the formula. It worked for the month of January but when I changed to the month of February it did not work. See the attached excel sheet.
Please try
=IFERROR(INDEX(TXs!$B$6:$H$1783,AGGREGATE(15,6,ROW(TXs!B$6:B$1783)/(TXs!$B$6:$B$1783>=$E$10)/(TXs!$B$6:$B$1783<EDATE($E$10,1)/(TXs!$I$6:$I$1783="Bank")),ROWS(B$19:B19))-ROW(TXs!B$5),IFNA(MATCH("*"&B$18&"*",TXs!$B$5:$H$5,),7)),"")
or for MS365
=FILTER(FILTER(TXs!B6:H1984,(TEXT(TXs!B6:B1984,"my")=TEXT(E10,"my"))*(TXs!I6:I1984="Bank")),{1,0,0,1,1,1,1})
Hi Ro_Ry
Thank you so much. The formula is working well but the Filter function is not it shows #SPLILL!.
If you have anything in the way (below, for example) of the SPILL area, it will error out.
Hi Ali, Belinda2000 and Ro_Ry
Thank you very much for helping. Problem solved.
Regards,
Sunboy
Here is mine with the rectified aggregate function:
F19:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks