COUNTIF with DATES

[sparky2205]

Hi folks,
I have a spreadsheet with 2 tabs. See below.
Sheet1 contains Date Issued in Column B. This is in custom date format dd-mmm-yy.
Sheet 2 contains a summary of the results of Sheet1.
What I am trying to do is summarise in the table in Sheet2 by number of records issued by quarter.
To do this I am trying to compare the last 2 digits of the year in Sheet2 B1 with the last 2 digits of the values in Sheet1 Column B.
I am doing this in a COUNTIF.
But it appears RIGHT returns the last 2 digits of the date serial number from Column B while returning the last 2 digits of the actual date from Sheet2, which is causing a mismatch.
I would appreciate any assistance with this.

MREXCEL1.xlsx
	A	B
1	No.	Date Issued
2	1	05-Nov-19
3	2	18-Nov-19
4	3	18-Nov-19
5	4	25-Nov-19
6	5	05-Dec-19
7	6	12-Dec-19
8	7	13-Dec-19
9	8	08-Jan-20
10	9	10-Jan-20
11	10	22-Jan-20
12	11	11-Feb-20
13	12	11-Feb-20
14	13	03-Mar-20
15	14	06-Apr-20
16	15	23-Apr-20
17	16	18-May-20
18	17	25-May-20
19	18	03-Jul-20
20	19	23-Jul-20
21	20	20-Aug-20
22	21	31-Aug-20
23	22	04-Jan-21
24	23	07-Jan-21
25	24	12-Jan-21
26	25	02-Mar-21
27	26	15-Apr-21
28	27	11-Mar-21
29	28	11-Mar-21
30	29	16-Mar-21
31	30	15-Apr-21
32	31	21-May-21
33	32	16-Apr-21
34	33	30-Apr-21
35	34	12-May-21
36	35	11-May-21
37	36	26-Apr-21
38	37	20-Aug-21
39	38	07-Jan-22
40	39	03-Feb-22
41	40	11-Mar-22
42	41	03-Mar-22
43	42	07-Mar-22
44	43	29-Apr-22
45	44	16-Aug-22
46	45	04-Apr-22
47	46	21-Apr-22
48	47	13-Apr-22
49	48	26-Jul-22
50	49	11-Oct-22
51	50	21-Apr-22
52	51	21-Apr-22
53	52	09-Jan-23
54	53	18-Jan-23
55	54	03-Apr-23
56	55	20-Jan-23
57	56	01-Feb-23
58	57	27-Jun-23
59	58	01-Feb-23
60	59	14-Jun-23
61	60	06-Mar-23
62	61	08-May-23
63	62	15-Mar-23
64	63	19-Apr-23
65	64	28-Mar-23
66	65	03-Apr-23
67	66	27-Mar-23
68	67	11-Apr-23
69	68	02-Jan-24
70	69	19-Feb-24
71	70	16-Jan-24
72	71	13-Feb-24
73	72	19-Feb-24
74	73	26-Feb-24
75	74	26-Feb-24
76	75	19-Feb-24
77	76	22-Mar-24
78	77	26-Mar-24
79	78	22-Mar-24
80	79	11-Apr-24
81	80	29-Apr-24
82	81	10-Apr-24
83	82	05-May-24
84	83	15-May-24
85	84	29-Apr-24
86	85	29-Apr-24
Sheet1

MREXCEL1.xlsx
	A	B	C	D	E
1	Review Year	2025		Last used row	86
2			Month	Currently Open
3			Q1 2025
4			Q2 2025
5			Q3 2025
6			Q4 2025
Sheet2

Cell Formulas
Range	Formula
E1	E1	=LOOKUP(2,1/(NOT(ISBLANK(Sheet1!A:A))),ROW(Sheet1!A:A))
C3	C3	= "Q1 " & $B$1
C4	C4	= "Q2 " & $B$1
C5	C5	= "Q3 " & $B$1
C6	C6	= "Q4 " & $B$1

 

[KillerOfGiants]

I'll let you right the formula, but instead of comparing the last two digits, compare the Year from Sheet 1 to the last 4 on sheet 2. You may also need to compare the month from the Sheet1 dates as well to the quarter number (month 1, 2, and 3 will be Q1, etc).

 

[Cubist]

Try:

Book2
	A	B	C	D	E
1	Review Year	2025		Last used row	86
2			Month	Currently Open
3			Q1 2025	0
4			Q2 2025	0
5			Q3 2025	0
6			Q4 2025	0
Sheet8

Cell Formulas
Range	Formula
E1	E1	=LOOKUP(2,1/(NOT(ISBLANK(Sheet1!A:A))),ROW(Sheet1!A:A))
C3	C3	= "Q1 " & $B$1
D3:D6	D3	=SUM(--("Q"&ROUNDUP(MONTH(Sheet1!$B$2:$B$86)/3,0)&" "&YEAR(Sheet1!$B$2:$B$86)=C3))
C4	C4	= "Q2 " & $B$1
C5	C5	= "Q3 " & $B$1
C6	C6	= "Q4 " & $B$1