• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with MAXIF Formula for a Multiple Selection Criteria (9 selections)

F

Fadil

Member
I have a summary with dropdown boxes and accodring to the selection I make, the table changes since I have put SUMIF formulas. I have created a chart that reads the data that is filtered and the chart is modified automatically.

Now, the summary is just a summary of multiple projects that fetches data from another table, the data table where I consolidate all the projects.

The issue I am having is, I want to build a table in Sheet1 to pull the Top Ten Highest or Lowest projects from the Summary sheet.
For example I have the following selection criteria in the summary:
Dropdown Box 1: Default value is *, and I have options to select
Dropdown Box 2: Default value is *, and I have options to select
Dropdown Box 3: Default value is *, and I have options to select
Dropdown Box 4: Default value is *, and I have options to select
Dropdown Box 5: Default value is *, and I have options to select

According to the selection I make, I want a table to pull the highest value from the Summary sheet data table, but there could be cases that the combo box could be let as default value * in which case should pull everything from that field as long as it matches the other criterias selected.

I have put this formula and it returns the correct value only if all dropdown boxes have a criteria selected, but if the dropdown box is left the default value * it does not pick up any value hence it returns zero
{=LARGE((Summary!A2:A1000=Sheet1!B1)*(Summary!B2:B1000=Sheet1!B2)*(Summary!C2:C1000=Sheet1!B3)*(Summary!D2:D1000=Sheet1!B4)*(Summary!E2:E1000=Sheet1!B5)*(Summary!F2:F1000),1)}

In Summary sheet Columns A, B, C, D and E have a value that can be found in dropdown boxes of Sheet1 cells B1, B2, B3, B4 and B5 and under Summary sheet F column is the column that has the value I am trying to pull the max of it and the next ordering line (2nd lowest etc).

For more details look at the excel file I uploaded

Any advise.
Your help is greatly appreciated
 

Attachments

Last edited:
Somendra Misra said:
Hi Fadil,

Will putting an advanced filter through a macro and than doing this calculation is acceptable?

Regards,
Click to expand...
Hi,

Thank you for your time. I don't actually understand what is advanced filter through macro, but anything is acceptable just as long as I get the data ranked on top ten according to the criteria I have selected on the dropdown boxes. I can't play much with the dropdown boxes since the selection I make there populates the table below that I have it with SumIf formulas and I have two other sheets that execute command on load when I select those sheets and they display the data in accordance to what I selected.
I hope I am making sense and being able to explain the issue I am having.

Have you been able to see the attachment I have included? I have included a sample attachment to what the data might look like and what I am looking at to filter / rank as top ten

Regards,
Fadil
 
@Fadil

See the attached file. Things to note:

1. Hidden Sheet "DummySheet"
2. Named Ranges
3. Large Formula.
4. VBA Code.

Just advise if you have any difficulty in understanding this.

Regards,
 

Attachments

Hi,
Thank you very much for your help. I was able to modify the sheets you sent me and use them to my needs and now it is working perfectly

All the best
Fadil
 
You must log in or register to reply here.
Back
Top