Using a helpful thread (Excel Filter Function - choose certain columns as output) to successfully create a filtered table using selected columns I now need to add a second criterium to the filtered table.
I went for this option as it suited my needs most:
=FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)
I used a drop down field to determine "include" (so not a fixed value as per the above example). I now need to not just check the drop down but also exclude all lines which have a certain value in them ("C"). The column in question is both in the original array and in the filtered output table.
Background: The drop down selects the month I want to look at and I need to exclude all actions which are completed (i.e. have the letter "C" in that column).
Using a helpful thread (Excel Filter Function - choose certain columns as output) to successfully create a filtered table using selected columns I now need to add a second criterium to the filtered table.
I went for this option as it suited my needs most:
=FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)
I used a drop down field to determine "include" (so not a fixed value as per the above example). I now need to not just check the drop down but also exclude all lines which have a certain value in them ("C"). The column in question is both in the original array and in the filtered output table.
Background: The drop down selects the month I want to look at and I need to exclude all actions which are completed (i.e. have the letter "C" in that column).
Share Improve this question edited Mar 24 at 14:50 Mayukh Bhattacharya 27.8k9 gold badges29 silver badges42 bronze badges asked Mar 24 at 14:41 DaniDani 234 bronze badges1 Answer
Reset to default 3As per the instruction on Microsoft's Webstie on the use of FILTER() you simply multiply the two booleans together:
=FILTER(CHOOSE({1,2},B1:B7,D1:D7),($K$1:$K$7=$K$1)*($C$1:$C$7 = "whatyouwant"))
Also, with HSTACK() we no longer need the CHOOSE():
=FILTER(HSTACK(B1:B7,D1:D7),($K$1:$K$7=$K$1)*($C$1:$C$7 = "whatyouwant"))