Formula to count between 2 numbers matching month

[umirin]

hi all need help with a formula that can count between 2 numbers and match date you can see my current formulas are done manually just seeing if there is an easier way.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F
1	Date	Change
2	Friday, 1 January 2016	2.00
3	Saturday, 2 January 2016	4.00			Date	Over 2 but Under 5
4	Sunday, 3 January 2016	1.00			Janurary	3
5	Monday, 4 January 2016	3.00			March	2
6	Tuesday, 5 January 2016	5.00			July	4
7	Wednesday, 6 January 2016	4.00
8	Tuesday, 1 March 2016	7.00
9	Wednesday, 2 March 2016	3.00
10	Thursday, 3 March 2016	6.00
11	Friday, 4 March 2016	9.00
12	Saturday, 5 March 2016	4.00
13	Sunday, 6 March 2016	2.00
14	Wednesday, 13 July 2016	8.00
15	Thursday, 14 July 2016	3.00
16	Friday, 15 July 2016	7.00
17	Saturday, 16 July 2016	3.00
18	Sunday, 17 July 2016	5.00
19	Monday, 18 July 2016	2.00
20	Tuesday, 19 July 2016	3.00
21	Wednesday, 20 July 2016	4.00
22	Thursday, 21 July 2016	7.00
test

Cell Formulas
Range	Formula
F4	=COUNTIFS($B$2:$B$7,">2",$B$2:$B$7,"<5")
F5	=COUNTIFS($B$8:$B$13,">2",$B$8:$B$13,"<5")
F6	=COUNTIFS($B$14:$B$22,">2",$B$14:$B$22,"<5")

 

[FormR]

Hi, assuming those dates in column A are proper excel dates with special formatting and that you can change the months in E4:E7 to excel dates (the first of each month) then you could try something like this.

Excel 2013
	A	B	C	D	E	F
1	Date	Change
2	Friday, 01 Jan 2016	2
3	Saturday, 02 Jan 2016	4			Date	Over 2 but Under 5
4	Sunday, 03 Jan 2016	1			Jan-16	3
5	Monday, 04 Jan 2016	3			Mar-16	2
6	Tuesday, 05 Jan 2016	5			Jul-16	4
7	Wednesday, 06 Jan 2016	4
8	Tuesday, 01 Mar 2016	7
9	Wednesday, 02 Mar 2016	3
10	Thursday, 03 Mar 2016	6
11	Friday, 04 Mar 2016	9
12	Saturday, 05 Mar 2016	4
13	Sunday, 06 Mar 2016	2
14	Wednesday, 13 Jul 2016	8
15	Thursday, 14 Jul 2016	3
16	Friday, 15 Jul 2016	7
17	Saturday, 16 Jul 2016	3
18	Sunday, 17 Jul 2016	5
19	Monday, 18 Jul 2016	2
20	Tuesday, 19 Jul 2016	3
21	Wednesday, 20 Jul 2016	4
22	Thursday, 21 Jul 2016	7
Sheet1

Cell Formulas
Range	Formula
F4	=COUNTIFS(A:A,">="&E4,A:A,"<="&EOMONTH(E4,0),$B:$B,">2",$B:$B,"<5")

 

[NotoriousPopol]

Hi,

you could try COUNTIFS($B:$B,">2",$B:$B,"<5",TEXT($B:$B,"mmmm")=$E4). Press ctrl+shift+enter as this is an array formula. I'm not sure of the result tho...

 

[FormR]

I'm not sure of the result tho...

Hi, you could always try it but FYI: COUNTIFS() will only accept ranges for the criteria_range arguments.