COUNTIFS formula.

[Kayslover]

I have a membership spreadsheet (STO Members) that details when members paid their club subscription alongside the statement number that their payment shows on. This data is in columns B and C.

On another sheet called “Supporting Paid Print” the statement number and paid dated is copied by means of a VLOOKUP.

I am trying to count the number of payments received in sheet Supporting Paid Print that have been paid in Jan, Feb, Mar etc. and I have the total in Row 60.

I am using the formula =COUNTIFS(C3:C52,F3:F52,">="&Formula!D2,C3:C52,F3:F52,"<="&Formula!E2) but I keep getting the following error which I cannot work out.

I have tried the above formula but as follows and I get the same error

=COUNTIFS(C3:C52,F3:F52,">="&Formula!&D2,C3:C52,F3:F52,"<="&Formula!&E2)

View attachment 111866

Any assistance given to resolve this error would be appreciated.

Sample data included.

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G	H
1	Membership Number	Payment Statement number	Payment Received Date	Surname	First Name	Address	Town	Post Code
2	SM00001	448	01/01/24	Surname 1	First Name 1	Address 1	Town 1	Post Code 1
3	SM00002	448	02/01/24	Surname 2	First Name 2	Address 2	Town 2	Post Code 2
4	SM00003	449	12/01/24	Surname 3	First Name 3	Address 3	Town 3	Post Code 3
5	SM00004	450	10/02/24	Surname 4	First Name 4	Address 4	Town 4	Post Code 4
6	SM00005	447	01/04/24	Surname 5	First Name 5	Address 5	Town 5	Post Code 5
7	SM00006	447	03/04/24	Surname 6	First Name 6	Address 6	Town 6	Post Code 6
8	SM00007	216	02/11/24	Surname 7	First Name 7	Address 7	Town 7	Post Code 7
9	SM00008	450	01/05/24	Surname 8	First Name 8	Address 8	Town 8	Post Code 8
10	SM00009	450	04/05/24	Surname 9	First Name 9	Address 9	Town 9	Post Code 9
11	SM00010	449	01/06/24	Surname 10	First Name 10	Address 10	Town 10	Post Code 10
12	SM00011	449	10/06/24	Surname 11	First Name 11	Address 11	Town 11	Post Code 11
13	SM00012	449	01/07/24	Surname 12	First Name 12	Address 12	Town 12	Post Code 12
14	SM00013	449	10/07/24	Surname 13	First Name 13	Address 13	Town 13	Post Code 13
15	SM00014	500	10/08/24	Surname 14	First Name 14	Address 14	Town 14	Post Code 14
16	SM00015	500	10/08/24	Surname 15	First Name 15	Address 15	Town 15	Post Code 15
17	SM00016	523	01/09/24	Surname 16	First Name 16	Address 16	Town 16	Post Code 16
18	SM00017	563	29/09/24	Surname 17	First Name 17	Address 17	Town 17	Post Code 17
19	SM00018	603	01/10/24	Surname 18	First Name 18	Address 18	Town 18	Post Code 18
20	SM00019	643	10/10/24	Surname 19	First Name 19	Address 19	Town 19	Post Code 19
21	SM00020	683	10/10/24	Surname 20	First Name 20	Address 20	Town 20	Post Code 20
22	SM00021	723	01/11/24	Surname 21	First Name 21	Address 21	Town 21	Post Code 21
23	SM00022	763	09/11/24	Surname 22	First Name 22	Address 22	Town 22	Post Code 22
24	SM00023	803	11/11/24	Surname 23	First Name 23	Address 23	Town 23	Post Code 23
25	SM00024	843	01/12/24	Surname 24	First Name 24	Address 24	Town 24	Post Code 24
26	SM00025	883	05/12/24	Surname 25	First Name 25	Address 25	Town 25	Post Code 25
27	SM00026	923	05/12/24	Surname 26	First Name 26	Address 26	Town 26	Post Code 26
28	SM00027			Surname 27	First Name 27	Address 27	Town 27	Post Code 27
29	SM00028			Surname 28	First Name 28	Address 28	Town 28	Post Code 28
30	SM00029			Surname 29	First Name 29	Address 29	Town 29	Post Code 29
31	SM00030	628	01/01/24	Surname 30	First Name 30	Address 30	Town 30	Post Code 30
32	SM00031			Surname 31	First Name 31	Address 31	Town 31	Post Code 31
33	SM00032			Surname 32	First Name 32	Address 32	Town 32	Post Code 32
34	SM00033			Surname 33	First Name 33	Address 33	Town 33	Post Code 33
35	SM00034			Surname 34	First Name 34	Address 34	Town 34	Post Code 34
36	SM00035			Surname 35	First Name 35	Address 35	Town 35	Post Code 35
37	SM00036	628	01/01/24	Surname 36	First Name 36	Address 36	Town 36	Post Code 36
38	SM00037			Surname 37	First Name 37	Address 37	Town 37	Post Code 37
STO Members

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G	H	I
1	Supporting Members Paid Print
2	M/Ship No	Statement No	Paid Date	M/Ship No	Statement No	Paid Date	M/Ship No	Statement No	Paid Date
3	SM00001	448	01/01/24	SM00051			SM00101
4	SM00002	448	02/01/24	SM00052			SM00102
5	SM00003	449	12/01/24	SM00053			SM00103
6	SM00004	450	10/02/24	SM00054			SM00104
7	SM00005	447	01/04/24	SM00055			SM00105
8	SM00006	447	03/04/24	SM00056			SM00106
9	SM00007	216	02/11/24	SM00057			SM00107
10	SM00008	450	01/05/24	SM00058			SM00108
11	SM00009	450	04/05/24	SM00059			SM00109
12	SM00010	449	01/06/24	SM00060			SM00110
13	SM00011	449	10/06/24	SM00061			SM00111
14	SM00012	449	01/07/24	SM00062			SM00112
15	SM00013	449	10/07/24	SM00063			SM00113
16	SM00014	500	10/08/24	SM00064			SM00114
17	SM00015	500	10/08/24	SM00065			SM00115
18	SM00016	523	01/09/24	SM00066			SM00116
19	SM00017	563	29/09/24	SM00067			SM00117
20	SM00018	603	01/10/24	SM00068			SM00118
21	SM00019	643	10/10/24	SM00069			SM00119
22	SM00020	683	10/10/24	SM00070			SM00120
23	SM00021	723	01/11/24	SM00071			SM00121
24	SM00022	763	09/11/24	SM00072			SM00122
25	SM00023	803	11/11/24	SM00073			SM00123
26	SM00024	843	01/12/24	SM00074			SM00124
27	SM00025	883	05/12/24	SM00075			SM00125
28	SM00026	923	05/12/24	SM00076			SM00126
29	SM00027			SM00077			SM00127
30	SM00028			SM00078			SM00128
31	SM00029			SM00079			SM00129
32	SM00030	628	01/01/24	SM00080			SM00130
33	SM00031			SM00081			SM00131
34	SM00032			SM00082			SM00132
35	SM00033			SM00083			SM00133
36	SM00034			SM00084			SM00134
37	SM00035			SM00085			SM00135
38	SM00036	628	01/01/24	SM00086			SM00136
39	SM00037			SM00087			SM00137
40	SM00038			SM00088			SM00138
41	SM00039			SM00089			SM00139
42	SM00040			SM00090			SM00140
43	SM00041			SM00091			SM00141
44	SM00042			SM00092			SM00142
45	SM00043			SM00093			SM00143
46	SM00044			SM00094			SM00144
47	SM00045			SM00095			SM00145
48	SM00046			SM00096			SM00146
49	SM00047			SM00097			SM00147
50	SM00048			SM00098			SM00148
51	SM00049			SM00099			SM00149
52	SM00050			SM00100			SM00150
53
54	Column Count	28	28		0	0		0	0
55
56	There is 1 person who made a payment on the 13/4/24 which did not have any reference number
57
58	Total Number of Paid Members				28
59	Paid In Jan	Paid In Feb	Paid In Mar	Paid In Apr	Paid In May	Paid In June	Paid In July	Paid In Aug	Paid In Sept
60
Supporting Paid Print

Cell Formulas
Range	Formula
H3:H52,E3:E52,B3:B52	B3	=IF(VLOOKUP(A3,'STO Members'!$A$2:$C$203,2,FALSE)=0,"",VLOOKUP(A3,'STO Members'!$A$2:$C$203,2,FALSE))
I3:I52,F3:F52,C3:C52	C3	=IF(VLOOKUP(A3,'STO Members'!$A$2:$C$203,3,FALSE)=0,"",VLOOKUP(A3,'STO Members'!$A$2:$C$203,3,FALSE))
H54:I54,E54:F54,B54:C54	B54	=COUNTIF(B3:B52,">0")
E58	E58	=B54+E54+H54+K54

2024 Membership - Copy.xlsm
	A	B	C	D	E
1	Processing Year	2024	24	Month Start Date	Month End Date
2	Start of Year	01/01/24	January	01/01/24	31/01/24
3			February	02/01/24	29/02/24
4			March	03/01/24	31/03/24
5			April	04/01/24	30/04/24
6			May	05/01/24	31/05/24
7			June	06/01/24	30/06/24
8			July	07/01/24	31/07/24
9			August	08/01/24	31/08/24
10			September	09/01/24	30/09/24
11			October	10/01/24	31/10/24
12			November	11/01/24	30/11/24
13			December	12/01/24	31/12/24
Formula

Cell Formulas
Range	Formula
C1	C1	=RIGHT(B1,2)
B2	B2	=CONCATENATE("01/","01/",$C$1)
D2	D2	=$B$2-DAY($B$2)+1
E2	E2	=EOMONTH($B$2,0)
D3	D3	=$B$2-DAY($B$2)+2
E3	E3	=EOMONTH($B$2,1)
D4	D4	=$B$2-DAY($B$2)+3
E4	E4	=EOMONTH($B$2,2)
D5	D5	=$B$2-DAY($B$2)+4
E5	E5	=EOMONTH($B$2,3)
D6	D6	=$B$2-DAY($B$2)+5
E6	E6	=EOMONTH($B$2,4)
D7	D7	=$B$2-DAY($B$2)+6
E7	E7	=EOMONTH($B$2,5)
D8	D8	=$B$2-DAY($B$2)+7
E8	E8	=EOMONTH($B$2,6)
D9	D9	=$B$2-DAY($B$2)+8
E9	E9	=EOMONTH($B$2,7)
D10	D10	=$B$2-DAY($B$2)+9
E10	E10	=EOMONTH($B$2,8)
D11	D11	=$B$2-DAY($B$2)+10
E11	E11	=EOMONTH($B$2,9)
D12	D12	=$B$2-DAY($B$2)+11
E12	E12	=EOMONTH($B$2,10)
D13	D13	=$B$2-DAY($B$2)+12
E13	E13	=EOMONTH($B$2,11)

 

[Kayslover]

RoryA, Arun,

Sorry for delay in responding, a couple of things came up during the weekend and hence the delay.
I have now had an opportunity to have a look at the formula and I am getting errors in the counts per month.

I have therefore created a new sheet called “Data”, and it contains the data that has been extrapolated from sheet “STO Members” that contains data in Columns A to C. It has been sorted so that all Jan, Feb, .. dates are in Column C and manually inserted the count in Column D.

In sheet “Supporting Paid Print” in row 63, I have manually inserted what the counts per month should be.

In row 60, I have inserted the formula

=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$2,'STO Members'!$C$2:$C$203,"<="&Formula!$E$2)

and in row 61 I have the formula

=COUNTIFS($C$3:$O$53,">="&Formula!$D2,$C$3:$O$53,"<="&Formula!$E2)

Both the formulas are producing the same figures, however they are incorrect.

Formula

=SUMPRODUCT((C3:O53>=Formula!D2)*(C3:O53<=Formula!E2)*(C2:O2="Paid Date"))

has been inserted in A66 and I am getting #N/A.

I attach sheet data for assist.

Any further assistance will be appreciated.

Thanks,

Pank

2024 Membership - Copy.xlsm
	A	B	C	D
1	Membership Number	Payment Statement number	Payment Received Date	Count Paid in Month
2	SM00001	448	01/01/24
3	SM00030	628	01/01/24
4	SM00036	628	01/01/24	7
5	SM00050	628	01/01/24
6	SM00051	628	01/01/24
7	SM00002	448	02/01/24
8	SM00003	449	12/01/24
9
10	SM00004	450	10/02/24	1
11
12	SM00005	447	01/04/24	2
13	SM00006	447	03/04/24
14
15	SM00008	450	01/05/24	2
16	SM00009	450	04/05/24
17
18	SM00010	449	01/06/24	2
19	SM00011	449	10/06/24
20
21	SM00012	449	01/07/24	2
22	SM00013	449	10/07/24
23
24	SM00014	500	10/08/24	2
25	SM00015	500	10/08/24
26
27	SM00016	523	01/09/24	2
28	SM00017	563	29/09/24
29
30	SM00018	603	01/10/24	3
31	SM00019	643	10/10/24
32	SM00020	683	10/10/24
33
34	SM00021	723	01/11/24	4
35	SM00007	216	02/11/24
36	SM00022	763	09/11/24
37	SM00023	803	11/11/24
38
39	SM00024	843	01/12/24	3
40	SM00025	883	05/12/24
41	SM00026	923	05/12/24
42	SM00027
Data

Data from sheet Supporting Paid Print

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G	H	I
2	M/Ship No	Statement No	Paid Date	M/Ship No	Statement No	Paid Date	M/Ship No	Statement No	Paid Date
3	SM00001	448	01/01/24	SM00051	628	01/01/24	SM00101
4	SM00002	448	02/01/24	SM00052			SM00102
5	SM00003	449	12/01/24	SM00053			SM00103
6	SM00004	450	10/02/24	SM00054			SM00104
7	SM00005	447	01/04/24	SM00055			SM00105
8	SM00006	447	03/04/24	SM00056			SM00106
9	SM00007	216	02/11/24	SM00057			SM00107
10	SM00008	450	01/05/24	SM00058			SM00108
11	SM00009	450	04/05/24	SM00059			SM00109
12	SM00010	449	01/06/24	SM00060			SM00110
13	SM00011	449	10/06/24	SM00061			SM00111
14	SM00012	449	01/07/24	SM00062			SM00112
15	SM00013	449	10/07/24	SM00063			SM00113
16	SM00014	500	10/08/24	SM00064			SM00114
17	SM00015	500	10/08/24	SM00065			SM00115
18	SM00016	523	01/09/24	SM00066			SM00116
19	SM00017	563	29/09/24	SM00067			SM00117
20	SM00018	603	01/10/24	SM00068			SM00118
21	SM00019	643	10/10/24	SM00069			SM00119
22	SM00020	683	10/10/24	SM00070			SM00120
23	SM00021	723	01/11/24	SM00071			SM00121
24	SM00022	763	09/11/24	SM00072			SM00122
25	SM00023	803	11/11/24	SM00073			SM00123
26	SM00024	843	01/12/24	SM00074			SM00124
27	SM00025	883	05/12/24	SM00075			SM00125
28	SM00026	923	05/12/24	SM00076			SM00126
29	SM00027			SM00077			SM00127
30	SM00028			SM00078			SM00128
31	SM00029			SM00079			SM00129
32	SM00030	628	01/01/24	SM00080			SM00130
33	SM00031			SM00081			SM00131
34	SM00032			SM00082			SM00132
35	SM00033			SM00083			SM00133
36	SM00034			SM00084			SM00134
37	SM00035			SM00085			SM00135
38	SM00036	628	01/01/24	SM00086			SM00136
39	SM00037			SM00087			SM00137
40	SM00038			SM00088			SM00138
41	SM00039			SM00089			SM00139
42	SM00040			SM00090			SM00140
43	SM00041			SM00091			SM00141
44	SM00042			SM00092			SM00142
45	SM00043			SM00093			SM00143
46	SM00044			SM00094			SM00144
47	SM00045			SM00095			SM00145
48	SM00046			SM00096			SM00146
49	SM00047			SM00097			SM00147
50	SM00048			SM00098			SM00148
51	SM00049			SM00099			SM00149
52	SM00050	628	01/01/24	SM00100			SM00150
53
58	Total Number of Paid Members				30
Supporting Paid Print

Cell Formulas
Range	Formula
H3:H52,E3:E52,B3:B52	B3	=IF(VLOOKUP(A3,'STO Members'!$A$2:$C$203,2,FALSE)=0,"",VLOOKUP(A3,'STO Members'!$A$2:$C$203,2,FALSE))
I3:I52,F3:F52,C3:C52	C3	=IF(VLOOKUP(A3,'STO Members'!$A$2:$C$203,3,FALSE)=0,"",VLOOKUP(A3,'STO Members'!$A$2:$C$203,3,FALSE))
E58	E58	=B54+E54+H54+K54

I have had to show the formulas separately as selecting the mini sheet capture said I had too much data.

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
59	Paid In Jan	Paid In Feb	Paid In Mar	Paid In Apr	Paid In May	Paid In June	Paid In July	Paid In Aug	Paid In Sept	Paid In Oct	Paid In Nov	Paid In Dec
60	7	3	2	4	6	8	10	12	14	17	21	24
61	7	3	2	4	6	8	10	12	14	17	21	24
62
63	7	1	0	2	2	2	2	2	2	3	4	3	Manual Count
64
65
66	#N/A
Supporting Paid Print

Cell Formulas
Range	Formula
A60	A60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$2,'STO Members'!$C$2:$C$203,"<="&Formula!$E$2)
B60	B60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$3,'STO Members'!$C$2:$C$203,"<="&Formula!$E$3)
C60	C60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$4,'STO Members'!$C$2:$C$203,"<="&Formula!$E$4)
D60	D60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$5,'STO Members'!$C$2:$C$203,"<="&Formula!$E$5)
E60	E60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$6,'STO Members'!$C$2:$C$203,"<="&Formula!$E$6)
F60	F60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$7,'STO Members'!$C$2:$C$203,"<="&Formula!$E$7)
G60	G60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$8,'STO Members'!$C$2:$C$203,"<="&Formula!$E$8)
H60	H60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$9,'STO Members'!$C$2:$C$203,"<="&Formula!$E$9)
I60	I60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$10,'STO Members'!$C$2:$C$203,"<="&Formula!$E$10)
J60	J60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$11,'STO Members'!$C$2:$C$203,"<="&Formula!$E$11)
K60	K60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$12,'STO Members'!$C$2:$C$203,"<="&Formula!$E$12)
L60	L60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$13,'STO Members'!$C$2:$C$203,"<="&Formula!$E$13)
A61	A61	=COUNTIFS($C$3:$O$53,">="&Formula!$D2,$C$3:$O$53,"<="&Formula!$E2)
B61	B61	=COUNTIFS($C$3:$O$53,">="&Formula!$D3,$C$3:$O$53,"<="&Formula!$E3)
C61	C61	=COUNTIFS($C$3:$O$53,">="&Formula!$D4,$C$3:$O$53,"<="&Formula!$E4)
D61	D61	=COUNTIFS($C$3:$O$53,">="&Formula!$D5,$C$3:$O$53,"<="&Formula!$E5)
E61	E61	=COUNTIFS($C$3:$O$53,">="&Formula!$D6,$C$3:$O$53,"<="&Formula!$E6)
F61	F61	=COUNTIFS($C$3:$O$53,">="&Formula!$D7,$C$3:$O$53,"<="&Formula!$E7)
G61	G61	=COUNTIFS($C$3:$O$53,">="&Formula!$D8,$C$3:$O$53,"<="&Formula!$E8)
H61	H61	=COUNTIFS($C$3:$O$53,">="&Formula!$D9,$C$3:$O$53,"<="&Formula!$E9)
I61	I61	=COUNTIFS($C$3:$O$53,">="&Formula!$D10,$C$3:$O$53,"<="&Formula!$E10)
J61	J61	=COUNTIFS($C$3:$O$53,">="&Formula!$D11,$C$3:$O$53,"<="&Formula!$E11)
K61	K61	=COUNTIFS($C$3:$O$53,">="&Formula!$D12,$C$3:$O$53,"<="&Formula!$E12)
L61	L61	=COUNTIFS($C$3:$O$53,">="&Formula!$D13,$C$3:$O$53,"<="&Formula!$E13)
A66	A66	=SUMPRODUCT((C2:O52>=Formula!D2)*(C2:O52<=Formula!E2)*(C2:O2="Paid Date"))

 

[arun_eagertolearn]

Hi

It looks like that the issue is with the 'formula' sheet

While you want 02-01-2024 to be Feb 1st, what the system takes it to be is Jan 2nd
Same is the case with 03-01-2024 and so on

You may use

Start of year- 01/01/24 (without any formula), or you may use date(2000+c1,1,1)- this would yield date(2024,1,1) which is Jan 1st, 2024

For the individual months, pls use
d2=edate(b2,0)
d3=edate(b2,1)
d4=edate(b2,2) and so on

Please check and let know

Thanks

 

[Kayslover]

Arun,

Thank you for your time so far.

I spotted the problem with the Formula sheet late name night and have now changed the formulas for month start (Column F) as =DATE($B$1,1,1) for Jan, =DATE($B$1,2,1) for Feb .. The value of $B$1 is set to 2024.

I looked at your suggestion to use dates within the formulas rather than pointing to cells in sheet Formula. Therefore the formulas in sheet “Supporting Paid Print” for Jan became

=COUNTIFS($C$3:$O$52,">=01/01/24",$C$3:$O$52,"<=31/01/24")

. Used the same formula for Feb – Dec, changed the dates and I got the desired results. These formulas are in cells A68 to L68

I therefore applied formatting cells in columns D and E in sheet Formula to read

=TEXT($F2,"dd/mm/yy") and E2 to be =TEXT($G2,"dd/mm/yy")

.

Looked at the results in sheet “Supporting Paid Print” the total were as expected. Cells A60 to L60 and A61 to L61

I don’t know why, but it appears that search range for the dates have to be converted to TEXT.

If by doing the above is a fluke, and incorrect, then please let me know.

I will have some proper data within a month and will check to ensure the results are correct.

Regards and many thanks for your help.

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G	H	I
1	Supporting Members Paid Print
2	M/Ship No	Statement No	Paid Date	M/Ship No	Statement No	Paid Date	M/Ship No	Statement No	Paid Date
3	SM00001	448	01/01/24	SM00051	628	01/01/24	SM00101
4	SM00002	448	02/01/24	SM00052			SM00102
5	SM00003	449	12/01/24	SM00053			SM00103
6	SM00004	450	10/02/24	SM00054			SM00104
7	SM00005	447	01/04/24	SM00055			SM00105
8	SM00006	447	03/04/24	SM00056			SM00106
9	SM00007	216	02/11/24	SM00057			SM00107
10	SM00008	450	01/05/24	SM00058			SM00108
11	SM00009	450	04/05/24	SM00059			SM00109
12	SM00010	449	01/06/24	SM00060			SM00110
13	SM00011	449	10/06/24	SM00061			SM00111
14	SM00012	449	01/07/24	SM00062			SM00112
15	SM00013	449	10/07/24	SM00063			SM00113
16	SM00014	500	10/08/24	SM00064			SM00114
17	SM00015	500	10/08/24	SM00065			SM00115
18	SM00016	523	01/09/24	SM00066			SM00116
19	SM00017	563	29/09/24	SM00067			SM00117
20	SM00018	603	01/10/24	SM00068			SM00118
21	SM00019	643	10/10/24	SM00069			SM00119
22	SM00020	683	10/10/24	SM00070			SM00120
23	SM00021	723	01/11/24	SM00071			SM00121
24	SM00022	763	09/11/24	SM00072			SM00122
25	SM00023	803	11/11/24	SM00073			SM00123
26	SM00024	843	01/12/24	SM00074			SM00124
27	SM00025	883	05/12/24	SM00075			SM00125
28	SM00026	923	05/12/24	SM00076			SM00126
29	SM00027			SM00077			SM00127
30	SM00028			SM00078			SM00128
31	SM00029			SM00079			SM00129
32	SM00030	628	01/01/24	SM00080			SM00130
33	SM00031			SM00081			SM00131
34	SM00032			SM00082			SM00132
35	SM00033			SM00083			SM00133
36	SM00034			SM00084			SM00134
37	SM00035			SM00085			SM00135
38	SM00036	628	01/01/24	SM00086			SM00136
39	SM00037			SM00087			SM00137
40	SM00038			SM00088			SM00138
41	SM00039			SM00089			SM00139
42	SM00040			SM00090			SM00140
43	SM00041			SM00091			SM00141
44	SM00042			SM00092			SM00142
45	SM00043			SM00093			SM00143
46	SM00044			SM00094			SM00144
47	SM00045			SM00095			SM00145
48	SM00046			SM00096			SM00146
49	SM00047			SM00097			SM00147
50	SM00048			SM00098			SM00148
51	SM00049			SM00099			SM00149
52	SM00050	628	01/01/24	SM00100			SM00150
Supporting Paid Print

Cell Formulas
Range	Formula
H3:H52,E3:E52,B3:B52	B3	=IF(VLOOKUP(A3,'STO Members'!$A$2:$C$203,2,FALSE)=0,"",VLOOKUP(A3,'STO Members'!$A$2:$C$203,2,FALSE))
I3:I52,F3:F52,C3:C52	C3	=IF(VLOOKUP(A3,'STO Members'!$A$2:$C$203,3,FALSE)=0,"",VLOOKUP(A3,'STO Members'!$A$2:$C$203,3,FALSE))

Formulas in Sheet called "Supporting Paid Print" to get required results:-

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
58	Total Number of Paid Members				30
59	Paid In Jan	Paid In Feb	Paid In Mar	Paid In Apr	Paid In May	Paid In June	Paid In July	Paid In Aug	Paid In Sept	Paid In Oct	Paid In Nov	Paid In Dec
60	7	1	0	2	2	2	2	2	2	3	4	3	30
61	7	1	0	2	2	2	2	2	2	3	4	3	30
62
63	7	1	0	2	2	2	2	2	2	3	4	3	Manual Count
64
65
66	7	1	0	2	2	2	2	2	2	3	4	3	30	Recommended
67	7	1	0	2	2	2	2	2	2	3	4	3	30	New
Supporting Paid Print

Cell Formulas
Range	Formula
E58	E58	=B54+E54+H54+K54
A60	A60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$2,'STO Members'!$C$2:$C$203,"<="&Formula!$E$2)
B60	B60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$3,'STO Members'!$C$2:$C$203,"<="&Formula!$E$3)
C60	C60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$4,'STO Members'!$C$2:$C$203,"<="&Formula!$E$4)
D60	D60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$5,'STO Members'!$C$2:$C$203,"<="&Formula!$E$5)
E60	E60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$6,'STO Members'!$C$2:$C$203,"<="&Formula!$E$6)
F60	F60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$7,'STO Members'!$C$2:$C$203,"<="&Formula!$E$7)
G60	G60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$8,'STO Members'!$C$2:$C$203,"<="&Formula!$E$8)
H60	H60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$9,'STO Members'!$C$2:$C$203,"<="&Formula!$E$9)
I60	I60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$10,'STO Members'!$C$2:$C$203,"<="&Formula!$E$10)
J60	J60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$11,'STO Members'!$C$2:$C$203,"<="&Formula!$E$11)
K60	K60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$12,'STO Members'!$C$2:$C$203,"<="&Formula!$E$12)
L60	L60	=COUNTIFS('STO Members'!$C$2:$C$203,">="&Formula!$D$13,'STO Members'!$C$2:$C$203,"<="&Formula!$E$13)
M66:M67,M60:M61	M60	=SUM(A60:L60)
A61	A61	=COUNTIFS($C$3:$O$53,">="&Formula!$D2,$C$3:$O$53,"<="&Formula!$E2)
B61	B61	=COUNTIFS($C$3:$O$53,">="&Formula!$D3,$C$3:$O$53,"<="&Formula!$E3)
C61	C61	=COUNTIFS($C$3:$O$53,">="&Formula!$D4,$C$3:$O$53,"<="&Formula!$E4)
D61	D61	=COUNTIFS($C$3:$O$53,">="&Formula!$D5,$C$3:$O$53,"<="&Formula!$E5)
E61	E61	=COUNTIFS($C$3:$O$53,">="&Formula!$D6,$C$3:$O$53,"<="&Formula!$E6)
F61	F61	=COUNTIFS($C$3:$O$53,">="&Formula!$D7,$C$3:$O$53,"<="&Formula!$E7)
G61	G61	=COUNTIFS($C$3:$O$53,">="&Formula!$D8,$C$3:$O$53,"<="&Formula!$E8)
H61	H61	=COUNTIFS($C$3:$O$53,">="&Formula!$D9,$C$3:$O$53,"<="&Formula!$E9)
I61	I61	=COUNTIFS($C$3:$O$53,">="&Formula!$D10,$C$3:$O$53,"<="&Formula!$E10)
J61	J61	=COUNTIFS($C$3:$O$53,">="&Formula!$D11,$C$3:$O$53,"<="&Formula!$E11)
K61	K61	=COUNTIFS($C$3:$O$53,">="&Formula!$D12,$C$3:$O$53,"<="&Formula!$E12)
L61	L61	=COUNTIFS($C$3:$O$53,">="&Formula!$D13,$C$3:$O$53,"<="&Formula!$E13)
A66	A66	=COUNTIFS($C$3:$O$52,">=01/01/24",$C$3:$O$52,"<=31/01/24")
B66	B66	=COUNTIFS($C$3:$O$52,">=01/02/24",$C$3:$O$52,"<=29/02/24")
C66	C66	=COUNTIFS($C$3:$O$52,">=01/03/24",$C$3:$O$52,"<=31/03/24")
D66	D66	=COUNTIFS($C$3:$O$52,">=01/04/24",$C$3:$O$52,"<=30/04/24")
E66	E66	=COUNTIFS($C$3:$O$52,">=01/05/24",$C$3:$O$52,"<=31/05/24")
F66	F66	=COUNTIFS($C$3:$O$52,">=01/06/24",$C$3:$O$52,"<=30/06/24")
G66	G66	=COUNTIFS($C$3:$O$52,">=01/07/24",$C$3:$O$52,"<=31/07/24")
H66	H66	=COUNTIFS($C$3:$O$52,">=01/08/24",$C$3:$O$52,"<=31/08/24")
I66	I66	=COUNTIFS($C$3:$O$52,">=01/09/24",$C$3:$O$52,"<=30/09/24")
J66	J66	=COUNTIFS($C$3:$O$52,">=01/10/24",$C$3:$O$52,"<=31/10/24")
K66	K66	=COUNTIFS($C$3:$O$52,">=01/11/24",$C$3:$O$52,"<=30/11/24")
L66	L66	=COUNTIFS($C$3:$O$52,">=01/12/24",$C$3:$O$52,"<=31/12/24")
A67	A67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$2,$C$3:$O$52,"<="&Formula!$E$2)
B67	B67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$3,$C$3:$O$52,"<="&Formula!$E$3)
C67	C67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$4,$C$3:$O$52,"<="&Formula!$E$4)
D67	D67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$5,$C$3:$O$52,"<="&Formula!$E$5)
E67	E67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$6,$C$3:$O$52,"<="&Formula!$E$6)
F67	F67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$7,$C$3:$O$52,"<="&Formula!$E$7)
G67	G67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$8,$C$3:$O$52,"<="&Formula!$E$8)
H67	H67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$9,$C$3:$O$52,"<="&Formula!$E$9)
I67	I67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$10,$C$3:$O$52,"<="&Formula!$E$10)
J67	J67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$11,$C$3:$O$52,"<="&Formula!$E$11)
K67	K67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$12,$C$3:$O$52,"<="&Formula!$E$12)
L67	L67	=COUNTIFS($C$3:$O$52,">="&Formula!$D$13,$C$3:$O$52,"<="&Formula!$E$13)

2024 Membership - Copy.xlsm
	A	B	C	D	E	F	G
1	Processing Year	2024	24	Month Start Date Text	Month End Date Text	Month Start Date Formula	Month End Date Formula
2	Start of Year	01/01/24	January	01/01/24	31/01/24	01/01/24	31/01/24
3			February	01/02/24	29/02/24	01/02/24	29/02/24
4			March	01/03/24	31/03/24	01/03/24	31/03/24
5			April	01/04/24	30/04/24	01/04/24	30/04/24
6			May	01/05/24	31/05/24	01/05/24	31/05/24
7			June	01/06/24	30/06/24	01/06/24	30/06/24
8			July	01/07/24	31/07/24	01/07/24	31/07/24
9			August	01/08/24	31/08/24	01/08/24	31/08/24
10			September	01/09/24	30/09/24	01/09/24	30/09/24
11			October	01/10/24	31/10/24	01/10/24	31/10/24
12			November	01/11/24	30/11/24	01/11/24	30/11/24
13			December	01/12/24	31/12/24	01/12/24	31/12/24
Formula

Cell Formulas
Range	Formula
C1	C1	=RIGHT(B1,2)
B2	B2	=CONCATENATE("01/","01/",$C$1)
D2:D13	D2	=TEXT($F2,"dd/mm/yy")
E2:E13	E2	=TEXT($G2,"dd/mm/yy")
F2	F2	=DATE($B$1,1,1)
G2	G2	=EOMONTH($B$2,0)
F3	F3	=DATE($B$1,2,1)
G3	G3	=EOMONTH($B$2,1)
F4	F4	=DATE($B$1,3,1)
G4	G4	=EOMONTH($B$2,2)
F5	F5	=DATE($B$1,4,1)
G5	G5	=EOMONTH($B$2,3)
F6	F6	=DATE($B$1,5,1)
G6	G6	=EOMONTH($B$2,4)
F7	F7	=DATE($B$1,6,1)
G7	G7	=EOMONTH($B$2,5)
F8	F8	=DATE($B$1,7,1)
G8	G8	=EOMONTH($B$2,6)
F9	F9	=DATE($B$1,8,1)
G9	G9	=EOMONTH($B$2,7)
F10	F10	=DATE($B$1,9,1)
G10	G10	=EOMONTH($B$2,8)
F11	F11	=DATE($B$1,10,1)
G11	G11	=EOMONTH($B$2,9)
F12	F12	=DATE($B$1,11,1)
G12	G12	=EOMONTH($B$2,10)
F13	F13	=DATE($B$1,12,1)
G13	G13	=EOMONTH($B$2,11)

 

[arun_eagertolearn]

Hi

I don’t know why, but it appears that search range for the dates have to be converted to TEXT.

I feel this is because the formulae have TEXT in them

You need not convert the dates into text, ie, the columns D&E in the sheet 'Formula' are not required

The countifs formula
=COUNTIFS($C$3:$O$52,">=01/01/24",$C$3:$O$52,"<=31/01/24")
could rather be
=COUNTIFS($C$3:$C$52,">="&Formula!$F$2,"<="&Formula!$G$2) for Jan

Thanks

 

[Kayslover]

Arun,

Thanks for your time so far.

Have used columns F and G as you have recommended.

Changed to your recommendations and "all is well".

Regards

Kayslover

P.S. Would also like to thank RoryA for his assistance.