Disregard argument in FILTER function

[ajocius1]

I am using FILTER function, where I define array and use multiple criteria that I want to use as filter for my result. Let say I use Country, Company and Year criteria to filter my data. Criteria is read from another cell where I have dropdown field with all possible values. It all works fine if I select for example Sweden, Volvo and 2021.

I would like to be disregard one of the three criteria's that I am using. So for example if I want to see Sweden, Volvo and then to see values for all years, what do I do. I know I can change formula to just use two criteria's instead of three, however I am looking for potential easier solution. I would like to enter something like % or * in the year criteria field to give me ALL values (in other words disregard that criteria).

Anyway to accomplish that?

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Disregard argument in FILTER function
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Peter_SSs]

Try like this

=FILTER(RangeToFilter,CountryRange=CountryOfInterest)*(CompanyRange=CompanyOfInterest)*(IF(YearOfInterest="*",1,YearRange=YearOfInterest)))

 

[ajocius1]

Yes, thought about IF statement to get it done, but was hopping that there is some symbol or symbol combo to tell excel to filter "nothing" (leave all values). One can use * or % in SQL query for example. Even though IF solution works, I have quite many FILTER formulas with 3-5 Filters, so was looking for possible shortcut here.

 

[Peter_SSs]

The FILTER function does not have a direct wildcard facility as you seem to be seeking. Here is another possible work-around approach that may be of use in some limited circumstances.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

22 02 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Country	Company	Year		Country	Company	Year		Country	Company	Year
2	a	h	2023		*	*	*		a	h	2023
3	b	g	2022						b	g	2022
4	c	h	2023						c	h	2023
5	d	g	2022						d	g	2022
6	c	h	2023						c	h	2023
7	b	g	2023						b	g	2023
8	d	h	2022						d	h	2022
9	a	g	2022						a	g	2022
10	a	h	2020						a	h	2020
11	c	g	2023						c	g	2023
12	d	h	2020						d	h	2020
13	b	g	2020						b	g	2020
14	c	h	2022						c	h	2022
15	b	g	2022						b	g	2022
16	c	h	2020						c	h	2020
17	c	g	2021						c	g	2021
18	c	h	2022						c	h	2022
FILTER

Cell Formulas
Range	Formula
I2:K18	I2	=FILTER(A2:C18,(SEARCH(E2&"|",A2:A18&"|"&E2&"|")=1)*(SEARCH(F2&"|",B2:B18&"|"&F2&"|")=1)*(SEARCH(G2&"|",C2:C18&"|"&G2&"|")=1))
Dynamic array formulas.

 

[Fluff]

@ajocius1 maybe you missed what Peter said here

If you have posted the question at more places, please provide links to those as well.

This is also posted here Disregard argument in FILTER function amongst other sites.
Please supply links to all other sites where you have asked this question. Thanks

 

[ajocius1]

@ajocius1 maybe you missed what Peter said here

This is also posted here Disregard argument in FILTER function amongst other sites.
Please supply links to all other sites where you have asked this question. Thanks

This question has been also posted on:

Disregard argument in FILTER function

I am using FILTER function, where I define array and use multiple criteria that I want to use as filter for my result. Let say I use Country, Company and Year criteria to filter my data. Criteria is read from another cell where I have dropdown field with all possible values. It all works fine if...

www.excelguru.ca

Disregard argument in FILTER function

Great video tutorials. I am using FILTER function, where I define array and use multiple criteria that I want to use as filter for my result. Let say I use Country, Company and Year criteria to filter my data. Criteria is read from another cell where I have dropdown field with all possible...

chandoo.org

Disregard argument in FILTER function

I am using FILTER function, where I define array and use multiple criteria that I want to use as filter for my result. Let say I use Country, Company and Year criteria to filter my data. Criteria is read from another cell where I have dropdown field with all possible values. It all works fine if...

www.mrexcel.com

Disregard argument in FILTER function

I am using FILTER function, where I define array and use multiple criteria that I want to use as filter for my result. Let say I use Country, Company and Year criteria to filter my data. Criteria is read from another cell where I have dropdown field with all possible values. It all works fine if...

www.excelforum.com

 

[Fluff]

Thanks for that.