Excel formula based on multiple criteria

Dear Experts,

Please find here attached workbook, I am looking for the formula in the Report tab where i am expecting the results based on First four drop downs from the Sales Raw Data tab. The fifth drop down is for changing the month for YTD Option.

Request to you kindly do help me out. Many thanks and kind regards,

Neilesh

Hard to tell, the drop downs do not really match the columns in the other sheet.

Yes, that would be a good idea

You can do it.

I really think that this would be a total nightmare. There are simply too many variables to account for. I know that I have done simpler versions of this for you in the past, but looking at your attachment at Post 1.... I am not sure I want to touch this one at all.

My suggestion:

Lower your expectations, redesign and simplify this a bit and re-post.

As Dave and Glenn have suggested, the drop-downs are unworkable, particularly your date dropdown. Why not just use a start and end date instead of several different date formats referring to several different columns? A start and end date would allow you to search by quarter, YTD, month, or any other period of time. It also makes the formula straightforward. With a start date in B4 and an end date in C4, you can use the following array-entered (confirm with Ctrl+Shift+Enter instead of Enter) formula in B7:

=IFERROR(INDEX('Sales Raw Data'!$B$3:$W$7,SMALL(IF(('Sales Raw Data'!$H$3:$H$7>=$B$4)*('Sales Raw Data'!$H$3:$H$7<=$C$4)*(IF($D$4="All",1,'Sales Raw Data'!$F$3:$F$7=$D$4))*(IF($E$4="All",1,'Sales Raw Data'!$D$3:$D$7=$E$4)),ROW('Sales Raw Data'!$D$3:$D$7)-ROW('Sales Raw Data'!$D$3)+1),ROW(1:1)),MATCH(Report!B$6,'Sales Raw Data'!$B$2:$W$2,0)),"")

Fill it down and across and it should return matches as desired based on the selections in row 4 and the headers. This would be a much simpler way to construct your search options. Take a look at the attachment to see if it gives you any ideas.

Dear Expert,

Thank you so much. I really appreciate your precious efforts. Yes you are absolutely right. With the current format i guess it would be a simply nightmare to expect the results based on the dropdowns. I will have to create a different tab to pull the data from the Sales Raw Data tab as per my requirement. I would try to pull in my required format based on the provided solution, where i may require your precious expertise for some formulae.

Thank you once again for your precious efforts and valuable support.

Many thanks and kind regards,

Neilesh

Except for the "Select Year" & "Select Manager" dropdowns, all the other drop downs do not match any column in sheet "Sales Raw Data".

Since this is the case, I have just given the code that will work for Select year and Select Manager and you will only get results if the values are found in the other sheet.

Please Login or Register  to view this content.

Dear Expert,

Thank you so much for your precious efforts and valuable support. Dear Sir, The Sales Raw Data tab is no more in picture for the current required formulae. I am expecting the results in Monthwise Summary tab for the four dropdowns will be completely based upon the Monthwise Breakup tab data. Request to you kindly see the second attached workbook.

Where i have simplified the process and created a monthwise breakup tab based on the Sales Raw Data tab. If you can please help me out for the formulae in Monthwise Summary Tab based on Monthwise Breakup Tab for the Four Dropdowns, would be really appreciated Experts.

One more help i am looking in Monthwise Breakup Tab for Column Z (i.e. the Average for the YTD Month, for eg. the Selected YTD Month is Aug So the expected results would Average from Jan To Aug Values) & for Column AA (i.e the immediate previous three months Average for the YTD Month, for eg. the Selected YTD Month currently is Aug so the Average Results would be for June, July & Aug Values).

Request to you kindly do provide your precious expertise and valuable support.

Many thanks and kind regards,

Neilesh

When you are creating a worksheet, you need to know ahead of time what information you want to extract from it. Right now the worksheet as it is, is not workable.

The drop downs in one sheet do not match the columns in the other sheet.

When you supply a workbook sample, you need to make sure people looking at it can understand what you are trying to do.

Dear Sir,

Apologies for the confusion, Please find here attached Revised workbook, Where i am expecting for the formulae in Monthwise Summary Tab from Cell B9 to K39 based on four dropdowns and the formulae will be based from Monthwise Breakup tab table.

As well Sir, In Monthwise Breakup Tab i am expecting the formulae for Column Z at Cell Z3 and the results would be based on the YTD Avg. up to Selected month from Monthwise Summary tab (for eg if the Selected YTD month in Monthwise Summary tab is August then the expected results would be Average from Jan To Aug Values) & for Column AA at Cell AA3 i am expecting results for 3 Months Average based on the Selected YTD Month (for eg. if the Selected YTD Month in Monthwise Summary tab is August then the Average Results would be for June, July & Aug Values).

I hope sir, i have explained well my requirement. Request to you please do help me out.

Request to you kindly do provide your precious expertise and valuable support.

Many thanks and kind regards,

Neilesh

I think it is best if you don't send us files with password protected VBA that (appears to) have on event code...

As far as I can see, you still have not simplified your requirements. The drop-downs are exactly the same, I think, as they were at the start. You are not going to get any help unless you follow the advice given, and patience will run thin if you continue to ignore it. Please review, and act upon, the advice in posts #9 and #10: create a simplified interface with drop-downs that have fewer options.

I think you are missing the point. The drop-down that complicates this is not the year drop-down but the period drop-down, which has all of these options that are nigh on impossible to cater for in a formula:

Excel 2016 (Windows) 32 bit

	C
4	Full Year
5	Current Month
6	Prior Month
7	Year to Date October
8	Year to Date September
9	Jan
10	Feb
11	Mar
12	Apr
13	May
14	Jun
15	Jul
16	Aug
17	Sep
18	Oct
19	Nov
20	Dec
21	1Q//17
22	2Q//17
23	3Q//17
24	4Q//17
25	YTD Aug

Sheet: Working

I do understand the criticality of the formulae and i have tried my level as well then only i have moved it to the Experts for resolving.

What does this mean? You have had two gurus, one expert and a former expert turned moderator (me) look at this so far and advise you that you need to rethink. It seems clear to me that you do not understand what we are saying, or think that we don't know what we are talking about. Which is it?

Thank you for your precious phase and valuable support.

I am curious to know what you mean by 'phase'?

Having re-read your last post, I think you are expecting one of us to reconfigure your workbook for you. That's not going to happen. For that level of support, please consider posting in the commercial services section, where you can offer recompense to someone willing to take this on.

Originally Posted by Neilesh Kumar

My apologies, I do understand your point but i do not have any other idea to put in the different format where it can be solve my requirement.

Request to you if any other format for Monthwise Breakup tab, which can resolve my Monthwise Summary Dropdown results then request to you please do provide.

As you want the workbook completely reformatting so that a solution can be offered, I suggest you post in the commercial services section, where you can offer a small payment as an incentive.

I give up.

Is this a VBA question, or a formula question?

If the plan is to carry on with a date range and filters as you've indicated in post #32, you can add more filter terms by inserting additional clauses like this:

*(IF($D$4="All",1,'Raw Data'!$F$3:$F$7=$D$4))

Into the "SMALL(IF" portion of the formula. With the two new filters included, your formula in B7 (array-entered) would be:

=IFERROR(INDEX('Raw Data'!$B$3:$W$7,SMALL(IF(('Raw Data'!$H$3:$H$7>=$B$4)*('Raw Data'!$H$3:$H$7<=$C$4)*(IF($D$4="All",1,'Raw Data'!$F$3:$F$7=$D$4))*(IF($E$4="All",1,'Raw Data'!$D$3:$D$7=$E$4))*(IF($F$4="All",1,'Raw Data'!$G$3:$G$7=$F$4))*(IF($G$4="All",1,'Raw Data'!$B$3:$B$7=$G$4)),ROW('Raw Data'!$D$3:$D$7)-ROW('Raw Data'!$D$3)+1),ROW(1:1)),MATCH(Report!B$6,'Raw Data'!$B$2:$W$2,0)),"")

Don't forget to select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I'm glad I could help, good luck!