Count Unique Values if Max Date between criteria

[KL28]

I have a table of data and need to count unique values in column A where the max date is between my criteria dates.

For example, here if my date criteria was >=01-May-22 AND <=31-May-22
then the formula will return 3 (Jack, Sarah, Jane)


If my date criteria was >=01-Jun-22 AND <=30-Jun-22 the formula will return 3 (Peter, Matt, Kelly)

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, please consider the following:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this is what you want.

22 12 18.xlsm
	A	B	C	D	E	F
1				Start	End	Count
2	Sarah	01-May-22		01-May-22	31-May-22	3
3	Jane	03-May-22		01-Jun-22	30-Jun-22	3
4	Matt	04-May-22		01-Jul-22	31-Jul-22	0
5	Jack	08-May-22
6	Sarah	12-May-22
7	Kelly	20-May-22
8	Sarah	25-May-22
9	Peter	26-May-22
10	Jane	28-May-22
11	Peter	05-Jun-22
12	Matt	11-Jun-22
13	Kelly	14-Jun-22
Count

Cell Formulas
Range	Formula
F2:F4	F2	=IFNA(ROWS(FILTER(A$2:A$13,(B$2:B$13=MAXIFS(B$2:B$13,A$2:A$13,A$2:A$13))*(B$2:B$13>=D2)*(B$2:B$13<=E2),NA())),0)

 

[kvsrinivasamurthy]

Try this

=SUMPRODUCT((B2:B13>=DATE(2022,5,1))*(B2:B13<=EOMONTH(DATE(2022,5,1),0)))

 

[Peter_SSs]

Try this

=SUMPRODUCT((B2:B13>=DATE(2022,5,1))*(B2:B13<=EOMONTH(DATE(2022,5,1),0)))

Perhaps you missed this?

need to count unique values in column A where the max date is between my criteria dates.

if my date criteria was >=01-May-22 AND <=31-May-22
then the formula will return 3 (Jack, Sarah, Jane)