Question:
For the data table below I'd like to count the number of staff leads who had at least one encounter in a specific time frame and the number of these who's last encounter was in a specific region
:Dee
For the data table below I'd like to count the number of staff leads who had at least one encounter in a specific time frame and the number of these who's last encounter was in a specific region
- I'd like to accomplish this using a single cell formula/array formula with no helper cells/columns.
- My table/worksheet is dynamic and i'm interested in using workbook real estate for other data.
- If it helps in understanding the formula feel free to show the helper cells but use of helper cells is not the way I'd like to do this
- I'm not good at vba, so, while it may be an option it would be a steeper learning curve for me to understand and implement.
:Dee
# | goal | expected result | explanation |
---|---|---|---|
1 | count number of team members who had an encounter between 7/1/20 and 6/30/21 | 9/10 | Brad, Chin, Dane, Fran, Gigi, Pam, Sam, Sue, Tina, all had at least one encounter in this time frame Shiela did not |
2 | report the number of leads whose last encounter within the time frame was in the MidWest | 3/10 | The last encounter during this time frame for Brad-4/4/21, Sue-9/24/20 and Sam-10/3/20 were in Midwest All other leads completed a last encounter in other regions |
Date | Region | Lead | Customer | Product |
---|---|---|---|---|
10/14/2021 | MidWest* | Tina | Amazon | CIN Item |
1/7/2021 | SouthEast | Tina | Google Doc | COL Item |
10/26/2020 | PureWest | Chin | High Def | AIM Item |
5/20/2020 | PureNorth | Chin | OverDone | AIM Item |
9/24/2020 | MidWest* | Sue | High Def | RAD Item |
1/14/2021 | MidWest* | Fran | Economist | AIM Item |
4/3/2020 | PureWest | Tina | High Def | RAD Item |
1/20/2021 | NorthEast | Brad | Economist | DAB Item |
2/26/2020 | SouthEast | Tina | H and eMA | RAD Item |
12/16/2021 | MidWest* | Gigi | OverDone | AIM Item |
4/5/2020 | NorthEast | Brad | Amazon | AIM Item |
6/24/2020 | NorthEast | Tina | Yahoo Mag | RAD Item |
8/22/2021 | SouthEast | Chin | McLendon's | RAD Item |
11/9/2021 | NorthEast | Pam | Costco's | CIN Item |
3/23/2021 | MidWest* | Fran | High Def | XOL Item |
10/3/2020 | MidWest* | Sam | Peet's Nut | RAD Item |
4/4/2021 | MidWest* | Batt | Amazon | AIM Item |
1/5/2020 | NorthEast | Shelia | High Def | CIN Item |
11/6/2021 | NorthEast | Gigi | Women's | AIM Item |
9/25/2021 | SouthEast | Dane | Amazon | XOL Item |
11/12/2021 | MidWest* | Dane | Economist | RAD Item |
12/7/2020 | PureWest | Gigi | Google Doc | CIN Item |
11/5/2021 | PureNorth | Sue | Quality FC | AIM Item |
9/30/2021 | NorthEast | Gigi | Peet's Nut | CIN Item |
8/27/2020 | NorthEast | Fran | H and eMA | AIM Item |
8/18/2021 | NorthEast | Gigi | OverDone | XOL Item |
2/19/2020 | PureNorth | Tina | H and eMA | RAD Item |
12/11/2021 | NorthEast | Gigi | McLendon's | AIM Item |
3/17/2021 | NorthEast | Pam | McLendon's | COL Item |
12/1/2020 | PureNorth | Dane | Economist | RAD Item |
4/5/2021 | NorthEast | Fran | H and eMA | NEE Item |
6/4/2020 | MidWest* | Dane | Google Doc | AIM Item |
7/19/2021 | PureWest | Dane | Google Doc | AIM Item |
4/25/2020 | PureNorth | Sue | High Def | COL Item |
9/24/2021 | PureWest | Sue | OverDone | AIM Item |
6/17/2020 | MidWest* | Tina | McLendon's | NEE Item |