Hi,
I've been using the filter function to filter values from an array I have (as can be seen in cells L3, N3, and P3). I now want to use the filter function in cell R3 to filter active projects, that is, those projects which satisfy the criteria of being both "Closed Won" and that have start and end dates that encapsulate today's date (using the today() function). Is there a way to do this? Any help would be much appreciated!
I've been using the filter function to filter values from an array I have (as can be seen in cells L3, N3, and P3). I now want to use the filter function in cell R3 to filter active projects, that is, those projects which satisfy the criteria of being both "Closed Won" and that have start and end dates that encapsulate today's date (using the today() function). Is there a way to do this? Any help would be much appreciated!
| kanban.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | Opportunity Owner | Company | Fiscal Period | Status | Lead Source | Industry | Fee | Anticipated Start Date | Anticipated End Date | Suspect | Qualified | Closed Won | Active | ||||||||
| 2 | Steve Baggs | Green Company | Q1-2021 | Closed Won | GLG | Entertainment | £20,000 | 07/10/2020 | 31/11/2020 | Company | Fee | Company | Fee | Company | Fee | Company | Fee | ||||
| 3 | Gem Powell | Blue Company | Q4-2020 | Suspect | GLG | Finance | £100,000 | 03/05/2020 | 08/08/2020 | Blue Company | 100000 | Red Company | 57000 | Green Company | 20000 | ||||||
| 4 | Ricky Blizzard | Red Company | Q3-2020 | Qualified | EC | BioTech | £57,000 | 19/04/2020 | 19/05/2020 | Purple Company | 55000 | Silver Company | 29000 | Yellow Company | 88000 | ||||||
| 5 | Woody Wick | Yellow Company | Q3-2020 | Closed Won | Other | Retail | £88,000 | 04/05/2020 | 29/09/2020 | Pink Company | 40000 | Black Company | 37000 | ||||||||
| 6 | Gem Powell | White Company | Q3-2020 | Closed Lost | CC | Manufacturing | £67,000 | 20/04/2020 | 07/06/2020 | Gold Company | 77000 | Brown Company | 91000 | ||||||||
| 7 | David Hughes | Black Company | Q4-2020 | Closed Won | EC | Utilities | £37,000 | 06/06/2020 | 30/07/2020 | Grey Company | 48000 | ||||||||||
| 8 | Steve Baggs | Brown Company | Q4-2020 | Closed Won | Other | Banking | £91,000 | 20/05/2020 | 28/09/2020 | ||||||||||||
| 9 | Steve Baggs | Purple Company | Q3-2020 | Suspect | Other | Finance | £55,000 | 17/08/2020 | 17/12/2020 | ||||||||||||
| 10 | Woody Wick | Pink Company | Q3-2020 | Suspect | CC | Retail | £40,000 | 11/11/2020 | 09/12/2020 | ||||||||||||
| 11 | Gem Powell | Orange Company | Q2-2020 | Closed Lost | GLG | Manufacturing | £22,000 | 05/01/2020 | 30/02/2020 | ||||||||||||
| 12 | Ricky Blizzard | Silver Company | Q1-2020 | Qualified | EC | Utilities | £29,000 | 02/02/2020 | 17/04/2020 | ||||||||||||
| 13 | Steve Baggs | Gold Company | Q4-2020 | Suspect | Other | Entertainment | £77,000 | 20/09/2020 | 10/12/2020 | ||||||||||||
| 14 | Woody Wick | Grey Company | Q2-2020 | Closed Won | Other | Finance | £48,000 | 17/11/2019 | 31/12/2019 | ||||||||||||
Sheet3 | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L3:M6 | L3 | =FILTER(FILTER(B2:G14,D2:D14=L1),COUNTIF(L2:M2,B1:G1)) |
| N3:O4 | N3 | =FILTER(FILTER(B2:G14,D2:D14=N1),COUNTIF(N2:O2,B1:G1)) |
| P3:Q7 | P3 | =FILTER(FILTER(B2:G14,D2:D14=P1),COUNTIF(P2:Q2,B1:G1)) |
| Dynamic array formulas. | ||
