# Count Unique Values if Max Date between criteria



## KL28 (Dec 18, 2022)

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 (Dec 18, 2022)

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.xlsmABCDEF1StartEndCount2Sarah01-May-2201-May-2231-May-2233Jane03-May-2201-Jun-2230-Jun-2234Matt04-May-2201-Jul-2231-Jul-2205Jack08-May-226Sarah12-May-227Kelly20-May-228Sarah25-May-229Peter26-May-2210Jane28-May-2211Peter05-Jun-2212Matt11-Jun-2213Kelly14-Jun-22CountCell FormulasRangeFormulaF2:F4F2=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 (Dec 18, 2022)

Try this

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


----------



## Peter_SSs (Dec 18, 2022)

kvsrinivasamurthy said:


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


Perhaps you missed this?


KL28 said:


> 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)


----------

