Filter Function - Being able to filter by more than one category within a column

%3CLINGO-SUB%20id%3D%22lingo-sub-1554087%22%20slang%3D%22en-US%22%3EFilter%20Function%20-%20Being%20able%20to%20filter%20by%20more%20than%20one%20category%20within%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554087%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20search%20dashboard%20for%20a%20database%20that%20I%20have%20created.%26nbsp%3B%20The%20simplest%20way%20I%20have%20found%20to%20do%20this%20is%20to%20create%20drop%20down%20menus%20and%20then%20use%20the%20filter%20function%3A%20FILTER(array%2Cinclude%2C%5Bif_empty%5D).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20great%2C%20except%20I%20think%20my%20end%20users%20will%20want%20to%20be%20able%20to%20include%20multiple%20categories%20from%20withing%20one%20column%2C%20or%20filter%20by%20some%20of%20the%20columns%20but%20not%20by%20the%20others.%26nbsp%3B%20The%20way%20that%20I%20have%20organized%20it%20now%2C%20only%20forces%20me%20to%20include%20one%20category%20for%20each%20section.%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DFILTER(Source!A%3AAA%2C(Source!E%3AE%3D'Search%20Dashboard'!B2)*(Source!F%3AF%3DB3)*(Source!G%3AG%3D'Search%20Dashboard'!B4)%2C%22No%20Results%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20thought%20on%20how%20to%20accomplish%20this%3F%26nbsp%3B%20Or%20a%20different%20way%20of%20creating%20a%20user-friendly%20professional%20looking%20search%20dashboard%20for%20a%20database%20with%20a%20lot%20of%20information%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1554087%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554406%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20-%20Being%20able%20to%20filter%20by%20more%20than%20one%20category%20within%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744013%22%20target%3D%22_blank%22%3E%40CMUNSONMSFUSA%3C%2FA%3E%26nbsp%3B%2C%20something%20like%20this%3F%20If%20not%2C%20a%20sample%20file%20will%20be%20useful%20to%20visualize.%20Remember%20to%20remove%20all%20confidential%20info.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554455%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20-%20Being%20able%20to%20filter%20by%20more%20than%20one%20category%20within%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20in%20case%2C%20another%20variant%20for%20AND%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(Table2%2CCOUNTIFS(J2%3AJ4%2CTable2%5BAAA%5D)*COUNTIFS(K2%3AK4%2CTable2%5BBBB%5D)*COUNTIFS(L2%3AL4%2CTable2%5BCCC%5D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20not%20separate%20from%20headers%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(Table2%5B%23All%5D%2CCOUNTIFS(J1%3AJ4%2CTable2%5B%5B%23All%5D%2C%5BAAA%5D%5D)*COUNTIFS(K1%3AK4%2CTable2%5B%5B%23All%5D%2C%5BBBB%5D%5D)*COUNTIFS(L1%3AL4%2CTable2%5B%5B%23All%5D%2C%5BCCC%5D%5D))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554615%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20-%20Being%20able%20to%20filter%20by%20more%20than%20one%20category%20within%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20Nice!%20Using%20Countifs%20as%20filtering%20conditions.%20I%20learn%20something%20new%20everyday.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555368%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20-%20Being%20able%20to%20filter%20by%20more%20than%20one%20category%20within%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555368%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20that%20could%20be%20any%20formula%20which%20returns%20equivalent%20of%20TRUE%20and%20FALSE%2C%20the%20only%20point%20it%20shall%20return%20an%20array%20of%20the%20same%20size%20(height)%20as%20the%20main%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello all, 

 

I'm trying to create a search dashboard for a database that I have created.  The simplest way I have found to do this is to create drop down menus and then use the filter function: FILTER(array,include,[if_empty]).

 

It works great, except I think my end users will want to be able to include multiple categories from withing one column, or filter by some of the columns but not by the others.  The way that I have organized it now, only forces me to include one category for each section.

=FILTER(Source!A:AA,(Source!E:E='Search Dashboard'!B2)*(Source!F:F=B3)*(Source!G:G='Search Dashboard'!B4),"No Results")

 

Any thought on how to accomplish this?  Or a different way of creating a user-friendly professional looking search dashboard for a database with a lot of information? 

 

Thanks!

4 Replies

@CMUNSONMSFUSA , something like this? If not, a sample file will be useful to visualize. Remember to remove all confidential info.

@TheAntony 

Just in case, another variant for AND

=FILTER(Table2,COUNTIFS(J2:J4,Table2[AAA])*COUNTIFS(K2:K4,Table2[BBB])*COUNTIFS(L2:L4,Table2[CCC]))

If not separate from headers

=FILTER(Table2[#All],COUNTIFS(J1:J4,Table2[[#All],[AAA]])*COUNTIFS(K1:K4,Table2[[#All],[BBB]])*COUNTIFS(L1:L4,Table2[[#All],[CCC]]))

@Sergei Baklan , Nice! Using Countifs as filtering conditions. I learn something new everyday. Thank you!

@TheAntony 

In general that could be any formula which returns equivalent of TRUE and FALSE, the only point it shall return an array of the same size (height) as the main table.