Optional FILTER in FILTER() Function

Hello Friends,

I am back with another question for my Excel Project.

My Excel Worksheet uses =FILTER() Functions to get some data based on two criterias out of a larger table.

I would like to include a Field/ Dropdown with Names of Persons as an optional choice which should influence my FILTER function.
So basically a third optional criteria (Consider it if I choose something, dont consider it if empty.)

My current Function

=FILTER(Tabelle1[Customer];(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)+(Tabelle1[Owner]=Tabelle3!D4);"")

What I would like to add
Dropdown: NAMES
If there is a name in the dropwdown selected the function should adjust and check for the NAME and if I dont select a NAME the function should run as before.

Hope the explanation is somewhat clear.

Thank you very much for your help!

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

If you have the FILTER function, then your version is MS365, NOT Excel 2019 - please update your profile.

Try this:

=FILTER(Tabelle1[Customer];(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)*(Tabelle1[Owner]=WENN(Tabelle3!D4<>"";Tabelle3!D4;"*"));"")

Not tested, as your sample file is not complete enough to allow testing (no source data provided).

As I said, it's not tested, so there's not much point unless we can make it work. It's just a basic IF clause, anyway, and I am sure you understand those.

Provide a better sample workbook that I can test it on. Maybe D4 is not really empty ... So many things could be at play here.

That's even less use!

Sorry - I cannot help without seeing something more realistic.

Simply provide a cut-down version of your file INCLUDING source data with sensitive data desensitised. Don't expect me to concoct dummy data on your behalf.

You are getting FREE help here - it it's too much trouble for you to provide the requisite sample data, then it's certainly too much trouble for me to bother wasting my time on it. Sorry.

Mock up a sample of a handful of rows with A, B, C instead of names, etc. No-one wants to see your real data... but SOMETHING to go on would be useful!!

Try this:

=FILTER(Tabelle1[Customer],(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)*(IF(Tabelle3!D4="",1,Tabelle1[Owner]=Tabelle3!D4)),"")

Replace commas with semi-colons and IF with WENN:

=FILTER(Tabelle1[Customer];(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)*(WENN(Tabelle3!D4="";1;Tabelle1[Owner]=Tabelle3!D4));"")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

I am still working on the same Excel-Sheet and I am now struggling with additional conditional filters.

The current Solution

=FILTER(Tabelle1[Customer];(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)*(WENN(Tabelle3!D4="";1;Tabelle1[Owner]=Tabelle3!D4));"")

It works very well but I would like to add another WENN() / IF () Statement that is conditional.
I tried the following:

=FILTER(Tabelle1[Customer];(Tabelle1[Revenue Start]>=A4)*(Tabelle1[Revenue End]<=B4)*(WENN(D4="";1;Tabelle1[Owner]=D4))*(WENN(E4="";1;Tabelle1[Target]=E4));"")

Thank you very much for your help!

Please attach a sheet so that we an SEE what you have.

Is this what you wanted???


=FILTER(Tabelle1[Customer],(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)*(Tabelle1[Owner]=Tabelle3!D4))

You said "If they are empty" where? If the cells in sheet Tabelle 3 are blank?? Or in the main table. You are not providing a detailed explanation.

I think you'll need this sort of structure:

=FILTER(Tabelle1[Customer];(Tabelle1[Revenue Start]>=Tabelle3!A4)*(Tabelle1[Revenue End]<=Tabelle3!B4)*((WENN(Tabelle3!D4="";1;Tabelle1[Owner]=Tabelle3!D4)+(WENN(Tabelle3!D4="";1;Tabelle1[Owner]=Tabelle3!D4)+(WENN(Tabelle3!D4="";1;Tabelle1[Owner]=Tabelle3!D4)));"")

Change the red and blue sections for your extra criteria.