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)

 

[RoryA]

Your structure for the formula is wrong. COUNTIFS needs pairs of ranges and criteria. Your C3:C52 references don't have any corresponding criteria.

 

[Kayslover]

RoryA,

The error is :-

 

[Fluff]

You have too many arguments, maybe

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

 

[RoryA]

Sorry - I edited my post above after taking a closer look at your formula.

 

[Kayslover]

RoryA,

Thank you for your comments about the Structure of the formula being wrong.

Basically what I am trying to do is check the range in C3:C53, F3:F53,I3:I53, L3:L53 and O3:O53 (all headed Paid Date” and have a formula in A60 that counts all dates for Jan, B61 that counts all dates in Feb (I would change the reference to Formula!D2 to E2..) C61 that counts all dates in Mar etc. etc etc.

Is it possible to replace the incorrect formula with some other formula that would give me the desired result?

 

[RoryA]

Looking at your data, you could probably get away with checking all cells in the entire range against those dates:

=COUNTIFS(C3:O53,">="&Formula!D2,C3:O53,"<="&Formula!E2)

unless your statement numbers are likely to go into the 45k range?

Slightly safer option might be something like:

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

 

[arun_eagertolearn]

Hi Kayslover

Pls try countifs(C3:C52,">="&Formula!$D$2,C3:C52,"<="&Formula!$E$2)

This counts those entries in C3 to C52, which fall in the range FormulaD2 to FormulaE2 (ie, Jan 2024)

For Feb month, pls try countifs(F3:F52,">="&Formula!$D$3,F3:F52,"<="&Formula!$E$3) and so on

Please do let know if this has been of help

Thanks

 

[Kayslover]

RoryA,

Thank you for the above, I appreciate your time and effort.

I will have a look at your recommendation over the weekend and come back to you. (I have a planned dentist appointment and have therefore got to disappear.

 

[Kayslover]

Arun,

Thank you for your assistance.

I will have a look at your recommendation over the weekend and come back to you. (I have a planned dentist appointment and have therefore got to disappear.