Dear Experts, please find here attached sample workbook where i have set few criteria and based on the same i am trying to use a formula but it is not giving the correct output in the Report tab at Cell B7.
Request to you kindly help me out.
Many thanks and kind regards,
Neilesh
Sample workbook.xlsx
You have been asking for help here for long enough to know that your attachment needs to have manually mocked up results to show what you want. Two lines of source data are inadequate - we need at least 10.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Dear Expert, I do accept that I have been asked help since long time and I do appreciate the quick and perfect support.
The reason to mention less because I have already put the formula in the Report tab at cell B7 with the criteria.
Request to you please help me out.
Last edited by Neilesh Kumar; 03-20-2018 at 02:10 PM.
So, what you want us to do is try to unpick a complex formula that doesn't work to try to work out what you are expecting to see returned in that cell - is that it? Well, no - not going to do that, sorry.
I'll try again: please provide your expected outcomes MANUALLY mocked up.
Dear Expert, basis on the six criteria from cell B4 to I4 in the Report tab I was trying to pull the data from Master tab but it is not giving the correct output.
So, what is the correct output?
The correct output basis on the criteria should be A because it is covering under all the six criteria. Start and End date criteria is for month Column in the master tab and rest other 4 criteria I have mentioned the columns name in the report tab.
Of course your example is going to be A, since you've only included 2 lines of data that both have A as the owner.Originally Posted by Neilesh Kumar
As already requested please give more sample data, and mock-up your expected results with an explanation of the formula function.
Dear Experts, can you please help me out on the raised thread.
Dear Expert, the "Start and End Date" Criteria in the Report tab at cell B5 & C5 is for the "Month" column which is at column B in the Master tab.
The second criteria is "Sales Type" in the Report tab at cell F5 and that is for column G in the Master tab.
The Third criteria is "Account Status (Account)" in the Report tab at Cell G5 and that is for column P in the Master tab.
The Fourth criteria is "Product 1" in the Report tab at Cell H5 and that is for Column F in the Master tab.
The Fifth Criteria is "Owner" in the Report tab at Cell I5 and that is for column column A in the Master tab.
So based on above criteria i am looking for the output in Cell B7 in the Report tab.
Please do help me out.
Many thanks and kind regards,
Neilesh
You have been asked for more sample data. If you want us to help, then provide what we are asking for.
Dear Expert AliGW, I truly apologies for submission of sample data, but when i reached at work and was about to start for preparing the sample data the moment i saw the solution and that made me realize where i was mistaken.
I truly apologies for the same. Henceforth will certainly be taking care of.
Your formula should work as per scenario but you have not consider the range constantly.
Formula:
=IFERROR(INDEX(Master!$A$2:$AA$1000,SMALL(IF((Master!$B$2:$B$100>=$B$5)*(Master!$B$2:$B$1000<=$C$5)*(IF($F$5="All",1,Master!$G$2:$G$1000=$F$5))*(IF($I$5="All",1,Master!$A$2:$A$1000=$I$5))*(IF($G$5="All",1,Master!$P$2:$P$1000=$G$5))*(IF($H$5="All",1,Master!$F$2:$F$1000=$H$5)),ROW(Master!$A$2:$A$1000)-ROW(Master!$A$2)+1),ROW(1:1)),MATCH(Report!B$6,Master!$A$1:$AA$1,0)),"")
If you look above highlighted part that is 100 instead of 1000.
Give this try with CSE
Formula:=IFERROR(INDEX(Master!$A$2:$AA$1000,SMALL(IF((Master!$B$2:$B$1000>=$B$5)*(Master!$B$2:$B$1000<=$C$5)*(IF($F$5="All",1,Master!$G$2:$G$1000=$F$5))*(IF($I$5="All",1,Master!$A$2:$A$1000=$I$5))*(IF($G$5="All",1,Master!$P$2:$P$1000=$G$5))*(IF($H$5="All",1,Master!$F$2:$F$1000=$H$5)),ROW(Master!$A$2:$A$1000)-ROW(Master!$A$2)+1),ROW(1:1)),MATCH(Report!B$6,Master!$A$1:$AA$1,0)),"")
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Dear Expert shukla.ankur281190, Thank you so much for your precious phase and valuable support. Thanks a ton Sir.
Many thanks and kind regards,
Neilesh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Neilesh Kumar 
Register To Reply
Bookmarks