Hello everyone,
I have below table that can populate employee names using filter function, what I need to add is populating specific fields (like country and full time/part time fields) along with employee names. Is there anyway that I can do that using the same filter formula but adding more criteria to the function that can populate data whenever I add any fields name that match the data table field names?
I have below table that can populate employee names using filter function, what I need to add is populating specific fields (like country and full time/part time fields) along with employee names. Is there anyway that I can do that using the same filter formula but adding more criteria to the function that can populate data whenever I add any fields name that match the data table field names?
| Filter with mulitple criteria .xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Employee ID | Employee Name | Employee's Manager | Country | Region | Type | Full Time / Part time | Status | Managers Table | ||||||||
| 2 | 1 | A | JJ | Argentina | LTAM | R | Part time | Y | JJ | Employee Name | Country | Full Time / Part time | |||||
| 3 | 2 | B | KK | Ireland | EMEA | R | Part time | Y | KK | A | |||||||
| 4 | 3 | C | LL | Ireland | EMEA | R | Full time | Y | LL | B | |||||||
| 5 | 4 | D | MM | Ireland | EMEA | R | Full time | N | MM | C | |||||||
| 6 | 5 | E | NN | Ireland | EMEA | R | Full time | N | NN | M | |||||||
| 7 | 6 | F | OO | Ireland | EMEA | R | Full time | N | OO | N | |||||||
| 8 | 7 | G | PP | Ireland | EMEA | R | Full time | N | PP | O | |||||||
| 9 | 8 | H | JJ | Spain | EMEA | R | Full time | N | Z | ||||||||
| 10 | 9 | I | KK | Sweden | EMEA | R | Full time | N | AA | ||||||||
| 11 | 10 | J | LL | Argentina | LTAM | R | Full time | N | BB | ||||||||
| 12 | 11 | K | MM | United States of America | US & Canada | R | Full time | N | |||||||||
| 13 | 12 | L | NN | United States of America | US & Canada | R | Full time | N | |||||||||
| 14 | 13 | M | OO | United States of America | US & Canada | R | Full time | Y | |||||||||
| 15 | 14 | N | PP | United States of America | US & Canada | R | Full time | Y | |||||||||
| 16 | 15 | O | JJ | United States of America | US & Canada | R | Full time | Y | |||||||||
| 17 | 16 | P | KK | Spain | EMEA | R | Full time | N | |||||||||
| 18 | 17 | Q | LL | Sweden | EMEA | R | Full time | N | |||||||||
| 19 | 18 | R | MM | Singapore | APAC | R | Full time | N | |||||||||
| 20 | 19 | S | NN | Singapore | APAC | R | Full time | N | |||||||||
| 21 | 20 | T | OO | Spain | EMEA | R | Full time | N | |||||||||
| 22 | 21 | U | PP | Sweden | EMEA | R | Full time | N | |||||||||
| 23 | 22 | V | JJ | United Arab Emirates | EMEA | R | Full time | N | |||||||||
| 24 | 23 | X | KK | United Arab Emirates | EMEA | R | Full time | N | |||||||||
| 25 | 24 | Y | LL | United Arab Emirates | EMEA | R | Full time | N | |||||||||
| 26 | 25 | Z | MM | Argentina | LTAM | R | Full time | Y | |||||||||
| 27 | 26 | AA | NN | United Kingdom | EMEA | R | Full time | Y | |||||||||
| 28 | 27 | BB | OO | United States of America | US & Canada | R | Full time | Y | |||||||||
| 29 | 28 | CC | PP | United States of America | US & Canada | R | Full time | N | |||||||||
| 30 | 29 | DD | JJ | United States of America | US & Canada | R | Full time | N | |||||||||
| 31 | 30 | EE | KK | United States of America | US & Canada | R | Full time | N | |||||||||
| 32 | 31 | FF | LL | United States of America | US & Canada | C | Full time | N | |||||||||
| 33 | 32 | KKK | Y21 | United States of America | US & Canada | R | Full time | N | |||||||||
| 34 | 33 | LLL | Y21 | United Arab Emirates | EMEA | R | Part time | N | |||||||||
| 35 | 34 | PPP | Y21 | United Arab Emirates | EMEA | R | Part time | N | |||||||||
| 36 | 35 | UUU | Y21 | Argentina | LTAM | R | Part time | N | |||||||||
| 37 | 36 | RRR | Y21 | United Kingdom | EMEA | R | Part time | N | |||||||||
| 38 | 37 | TTT | K21 | United Kingdom | EMEA | C | Part time | Y | |||||||||
| 39 | 38 | SSS | K21 | Singapore | APAC | R | Part time | Y | |||||||||
| 40 | 39 | S21 | K21 | Spain | EMEA | R | Part time | Y | |||||||||
| 41 | 40 | S45 | K21 | Sweden | EMEA | R | Full time | Y | |||||||||
Sheet1 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| M3:M11 | M3 | =FILTER(B2:B41,(COUNTIF(J2:J8,C2:C41))*(F2:F41="R")*(H2:H41="Y")) |
| Dynamic array formulas. | ||
