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).

• excel
• excel-formula
• filter

Share Improve this question Follow edited Mar 24 at 14:50 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 24 at 14:41 DaniDani 2344 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

As 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"))