COUNTIFS or maybe VLOOKUP

[RodneyW]

My sheet named "Data" has a range of dates in column B that include MM/DD/YY as in 1/01/2022, 1/03/2022, 1/03/22, 1/06/2022, 3/06/2022 and so on.

My sheet named "Dashboard" in column B has the entire month as in 1/01/2022, 2/01/2022, 3/01/2022 and so on, it doesn't include the day but rather all days are considered "01".

B4 of this sheet has 1/01/22. In C4 of this sheet I need a formula that will look in Data $B:$B for anything and COUNT everything 1/01/2022 - 1/31-2022 and give me that number. Using the data in the first line of this post, the formula would produce 4 as the result. (note there are two entries for 1/03/2022)

I'm using this formula: =COUNTIFS(Data!$B:$B,">=1/01/22",Data!$B:$B,"<=1/31/22") and it works but I have to move down cell by cell and change the formula to =COUNTIFS(Data!$B:$B,">=2/01/22",Data!$B:$B,"<=2/28/22") and =COUNTIFS(Data!$B:$B,">=3/01/22",Data!$B:$B,"<=3/31/22") and so on, over and over. This is doable, but it's time consuming, especially if I need to go back several years.

Is there an alternative way to do this so a formula looks for anything in Data!$B:$B that includes anything that includes the MM/YY that are in column B of Dashboard?

Thanks in advance

 

[DanteAmor]

Assuming the dates in the "Data" sheet:

Dante Amor
	A	B	C
1		DATE
2		01/01/2022
3		01/03/2022
4		01/03/2022
5		01/06/2022
6		03/06/2022
7		03/07/2022
8		03/10/2022
9		04/15/2022
10
Data

Try the following in the Dashboard sheet:

Dante Amor
	A	B	C
1
2
3		MONTH	COUNT
4		01/01/2022	4
5		02/01/2022	0
6		03/01/2022	3
7		04/01/2022	1
8		05/01/2022	0
9		06/01/2022	0
10		07/01/2022	0
11		08/01/2022	0
12		09/01/2022	0
13		10/01/2022	0
14		11/01/2022	0
15		12/01/2022	0
Dashboard

Cell Formulas
Range	Formula
C4:C15	C4	=COUNTIFS(Data!B:B,">="&B4,Data!B:B,"<="&EOMONTH(B4,0))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[RodneyW]

Assuming the dates in the "Data" sheet:

Dante Amor
	A	B	C
1		DATE
2		01/01/2022
3		01/03/2022
4		01/03/2022
5		01/06/2022
6		03/06/2022
7		03/07/2022
8		03/10/2022
9		04/15/2022
10
Data

Try the following in the Dashboard sheet:

Dante Amor
	A	B	C
1
2
3		MONTH	COUNT
4		01/01/2022	4
5		02/01/2022	0
6		03/01/2022	3
7		04/01/2022	1
8		05/01/2022	0
9		06/01/2022	0
10		07/01/2022	0
11		08/01/2022	0
12		09/01/2022	0
13		10/01/2022	0
14		11/01/2022	0
15		12/01/2022	0
Dashboard

Cell Formulas
Range	Formula
C4:C15	C4	=COUNTIFS(Data!B:B,">="&B4,Data!B:B,"<="&EOMONTH(B4,0))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Worked like a charm THANK YOU

 

[Peter_SSs]

Worked like a charm THANK YOU

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.