Countif Max Match

[dnelley]

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

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

Thanks in advance,
: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

 

[jasonb75]

Something like this will do it. I copied your original table to A1 for testing.

copy.xlsx
	G	H	I
2	7/1/20	6/30/21	10
3		Midwest*	4
Sheet2

Cell Formulas
Range	Formula
I2	I2	=IFERROR(ROWS(UNIQUE(FILTER(C2:C37,(A2:A37>=G2)*(A2:A37<=H2)))),0)
I3	I3	=IFERROR(ROWS(UNIQUE(FILTER(C2:C37,(A2:A37>=G2)*(A2:A37<=H2)*(B2:B37=H3)))),0)