countif formula not counting today's date...

V

valmir

Board Regular
Joined
Feb 10, 2021
Messages
235
Office Version
  1. 365
Platform
  1. Windows
Hello guys
This is my sample formula:
=COUNTIFS(R3;">0";R3;"<"&TODAY())
And as the title says, it is not counting (it is ignoring) today's date.
Any help please?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Seems to work for me, can you provide some sample data and an explanation of what doesn't work for you?

Book2
RST
1
21
322/02/2024
4
5
Sheet1
Cell Formulas
RangeFormula
T2T2=COUNTIFS(R3,">0",R3,"<"&TODAY())
R3R3=TODAY()-10

Book2
RST
1
20
304/03/2024
4
5
Sheet1
Cell Formulas
RangeFormula
T2T2=COUNTIFS(R3,">0",R3,"<"&TODAY())
R3R3=TODAY()+1

Book2
RST
1
20
303/03/2024
4
5
Sheet1
Cell Formulas
RangeFormula
T2T2=COUNTIFS(R3,">0",R3,"<"&TODAY())
R3R3=TODAY()
 
Upvote 0
The formula is in R47. The result should be 10. However, it is ignoring both today's dates on R12 and R45:
Stats 2023-24 - Copy.xlsx
R
115
2SSC
3 
4 
5PET
627 fev 24
711,4
8RCL
92 mar 24
1011
11WIL
123 mar 24
1311,5
14KAB
152 mar 24
1611,4
17INT
182 mar 24
1911,4
20BMQ
2127 fev 24
2211,3
23SAG
247 mar 24
25 
26DHL
272 mar 24
28 
29PRI
307 mar 24
31 
32 
33 
34 
35UML
362 mar 24
3711,2
38ACA
39 
40 
41SRC
422 mar 24
4311
44DLS
453 mar 24
4611,4
478
APPS
Cell Formulas
RangeFormula
R2,R5,R8,R11,R14,R17,R20,R23,R26,R29,R32,R35,R38,R41,R44R2=INDIRECT($A3&"!$GK$19")
R3,R6,R9,R12,R15,R18,R21,R24,R27,R30,R33,R36,R39,R42,R45R3=INDIRECT($A3&"!$GI$19")
R4R4=IFERROR(VALUE(INDEX(INDIRECT($A3&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A3&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A3&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A3&"!$R$2:$FK$2"),0)+1))/10,"")
R7R7=IFERROR(VALUE(INDEX(INDIRECT($A6&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A6&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A6&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A6&"!$R$2:$FK$2"),0)+1))/10,"")
R10R10=IFERROR(VALUE(INDEX(INDIRECT($A9&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A9&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A9&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A9&"!$R$2:$FK$2"),0)+1))/10,"")
R13R13=IFERROR(VALUE(INDEX(INDIRECT($A12&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A12&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A12&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A12&"!$R$2:$FK$2"),0)+1))/10,"")
R16R16=IFERROR(VALUE(INDEX(INDIRECT($A15&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A15&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A15&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A15&"!$R$2:$FK$2"),0)+1))/10,"")
R19R19=IFERROR(VALUE(INDEX(INDIRECT($A18&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A18&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A18&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A18&"!$R$2:$FK$2"),0)+1))/10,"")
R22R22=IFERROR(VALUE(INDEX(INDIRECT($A21&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A21&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A21&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A21&"!$R$2:$FK$2"),0)+1))/10,"")
R25R25=IFERROR(VALUE(INDEX(INDIRECT($A24&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A24&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A24&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A24&"!$R$2:$FK$2"),0)+1))/10,"")
R28R28=IFERROR(VALUE(INDEX(INDIRECT($A27&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A27&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A27&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A27&"!$R$2:$FK$2"),0)+1))/10,"")
R31R31=IFERROR(VALUE(INDEX(INDIRECT($A30&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A30&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A30&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A30&"!$R$2:$FK$2"),0)+1))/10,"")
R34R34=IFERROR(VALUE(INDEX(INDIRECT($A33&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A33&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A33&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A33&"!$R$2:$FK$2"),0)+1))/10,"")
R37R37=IFERROR(VALUE(INDEX(INDIRECT($A36&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A36&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A36&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A36&"!$R$2:$FK$2"),0)+1))/10,"")
R40R40=IFERROR(VALUE(INDEX(INDIRECT($A39&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A39&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A39&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A39&"!$R$2:$FK$2"),0)+1))/10,"")
R43R43=IFERROR(VALUE(INDEX(INDIRECT($A42&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A42&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A42&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A42&"!$R$2:$FK$2"),0)+1))/10,"")
R46R46=IFERROR(VALUE(INDEX(INDIRECT($A45&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A45&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A45&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A45&"!$R$2:$FK$2"),0)+1))/10,"")
R47R47=COUNTIFS(R3,">0",R3,"<"&TODAY())+COUNTIFS(R6,">0",R6,"<"&TODAY())+COUNTIFS(R9,">0",R9,"<"&TODAY())+COUNTIFS(R12,">0",R12,"<"&TODAY())+COUNTIFS(R15,">0",R15,"<"&TODAY())+COUNTIFS(R18,">0",R18,"<"&TODAY())+COUNTIFS(R21,">0",R21,"<"&TODAY())+COUNTIFS(R24,">0",R24,"<"&TODAY())+COUNTIFS(R27,">0",R27,"<"&TODAY())+COUNTIFS(R30,">0",R30,"<"&TODAY())+COUNTIFS(R33,">0",R33,"<"&TODAY())+COUNTIFS(R36,">0",R36,"<"&TODAY())+COUNTIFS(R39,">0",R39,"<"&TODAY())+COUNTIFS(R42,">0",R42,"<"&TODAY())+COUNTIFS(R45,">0",R45,"<"&TODAY())
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3,D3:AG3,AK3:BB3,BD3:BH3,B6,D6:AG6,AK6:BB6,BD6:BH6,B9,D9:AG9,AK9:BB9,BD9:BH9,B12,D12:AG12,AK12:BB12,BD12:BH12,B15,D15:AG15,AK15:BB15,BD15:BH15,B18,D18:AG18,AK18:BB18,BD18:BH18,B21,D21:AG21,AK21:BB21,BD21:BH21,B24,D24:AG24,AK24:BB24,BD24:BH24,B27,D27:AG27Dates OccurringtodaytextNO
BD47:BH47,AK47:AV47,D47:AG47Cell Value=0textNO
AI4,AI7,AI10,AI13,AI16,AI19,AI22,AI25,AI28,AI31,AI34,AI37,AI40,AI43,AI46,BD4:BG4,BD7:BG7,BD10:BG10,BD13:BG13,BD16:BG16,BD19:BG19,BD22:BG22,BD25:BG25,BD28:BG28,BD31:BG31,BD34:BG34,BD37:BG37,BD40:BG40,BD43:BG43,BD46:BG46,AK46:AZ46,AK43:AZ43,AK40:AZ40Expression=IF(AND(B3>0;B4<9);TRUE;FALSE)textNO
B2:B46,D2:AI46Cell Value=0textNO
 
Upvote 0
Do
Excel Formula: 
=ISNUMBER(R12)
and
Excel Formula: 
=ISNUMBER(R45)
return TRUE or FALSE?
 
Upvote 0
Are you expecting them counted as you are asking for the dates previous to today in the formula not the dates less than/equal to the current date, and it is 3 March 2024 here
 
Upvote 0
MARK858 said:
Are you expecting them counted as you are asking for the dates previous to today in the formula not the dates less than/equal to the current date, and it is 3 March 2024
Click to expand...
I want counted all dates prior to today as well as today's date
 
Upvote 0
Then you need to use
Excel Formula: 
"<="&TODAY()
rather than
Excel Formula: 
"<"&TODAY()
 
Upvote 0
MARK858 said:
Then you need to use
Excel Formula: 
"<="&TODAY()
rather than
Excel Formula: 
"<"&TODAY()
Click to expand...
I have already tried that and it's still giving the same result:
Stats 2023-24 - Copy.xlsx
R
115
2SSC
3 
4 
5PET
627 fev 24
711,4
8RCL
92 mar 24
1011
11WIL
123 mar 24
1311,5
14KAB
152 mar 24
1611,4
17INT
182 mar 24
1911,4
20BMQ
2127 fev 24
2211,3
23SAG
247 mar 24
25 
26DHL
272 mar 24
28 
29PRI
307 mar 24
31 
32 
33 
34 
35UML
362 mar 24
3711,2
38ACA
39 
40 
41SRC
422 mar 24
4311
44DLS
453 mar 24
4611,4
478
APPS
Cell Formulas
RangeFormula
R2,R5,R8,R11,R14,R17,R20,R23,R26,R29,R32,R35,R38,R41,R44R2=INDIRECT($A3&"!$GK$19")
R3,R6,R9,R12,R15,R18,R21,R24,R27,R30,R33,R36,R39,R42,R45R3=INDIRECT($A3&"!$GI$19")
R4R4=IFERROR(VALUE(INDEX(INDIRECT($A3&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A3&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A3&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A3&"!$R$2:$FK$2"),0)+1))/10,"")
R7R7=IFERROR(VALUE(INDEX(INDIRECT($A6&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A6&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A6&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A6&"!$R$2:$FK$2"),0)+1))/10,"")
R10R10=IFERROR(VALUE(INDEX(INDIRECT($A9&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A9&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A9&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A9&"!$R$2:$FK$2"),0)+1))/10,"")
R13R13=IFERROR(VALUE(INDEX(INDIRECT($A12&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A12&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A12&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A12&"!$R$2:$FK$2"),0)+1))/10,"")
R16R16=IFERROR(VALUE(INDEX(INDIRECT($A15&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A15&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A15&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A15&"!$R$2:$FK$2"),0)+1))/10,"")
R19R19=IFERROR(VALUE(INDEX(INDIRECT($A18&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A18&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A18&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A18&"!$R$2:$FK$2"),0)+1))/10,"")
R22R22=IFERROR(VALUE(INDEX(INDIRECT($A21&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A21&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A21&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A21&"!$R$2:$FK$2"),0)+1))/10,"")
R25R25=IFERROR(VALUE(INDEX(INDIRECT($A24&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A24&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A24&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A24&"!$R$2:$FK$2"),0)+1))/10,"")
R28R28=IFERROR(VALUE(INDEX(INDIRECT($A27&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A27&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A27&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A27&"!$R$2:$FK$2"),0)+1))/10,"")
R31R31=IFERROR(VALUE(INDEX(INDIRECT($A30&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A30&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A30&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A30&"!$R$2:$FK$2"),0)+1))/10,"")
R34R34=IFERROR(VALUE(INDEX(INDIRECT($A33&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A33&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A33&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A33&"!$R$2:$FK$2"),0)+1))/10,"")
R37R37=IFERROR(VALUE(INDEX(INDIRECT($A36&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A36&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A36&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A36&"!$R$2:$FK$2"),0)+1))/10,"")
R40R40=IFERROR(VALUE(INDEX(INDIRECT($A39&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A39&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A39&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A39&"!$R$2:$FK$2"),0)+1))/10,"")
R43R43=IFERROR(VALUE(INDEX(INDIRECT($A42&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A42&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A42&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A42&"!$R$2:$FK$2"),0)+1))/10,"")
R46R46=IFERROR(VALUE(INDEX(INDIRECT($A45&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A45&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A45&"!$R$2:$FK$51"),50,MATCH(R1,INDIRECT($A45&"!$R$2:$FK$2"),0)+1))/10,"")
R47R47=COUNTIFS(R3,">0",R3,"<="&TODAY())+COUNTIFS(R6,">0",R6,"<="&TODAY())+COUNTIFS(R9,">0",R9,"<="&TODAY())+COUNTIFS(R12,">0",R12,"<="&TODAY())+COUNTIFS(R15,">0",R15,"<="&TODAY())+COUNTIFS(R18,">0",R18,"<="&TODAY())+COUNTIFS(R21,">0",R21,"<="&TODAY())+COUNTIFS(R24,">0",R24,"<="&TODAY())+COUNTIFS(R27,">0",R27,"<="&TODAY())+COUNTIFS(R30,">0",R30,"<="&TODAY())+COUNTIFS(R33,">0",R33,"<="&TODAY())+COUNTIFS(R36,">0",R36,"<="&TODAY())+COUNTIFS(R39,">0",R39,"<="&TODAY())+COUNTIFS(R42,">0",R42,"<="&TODAY())+COUNTIFS(R45,">0",R45,"<="&TODAY())
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3,D3:AG3,AK3:BB3,BD3:BH3,B6,D6:AG6,AK6:BB6,BD6:BH6,B9,D9:AG9,AK9:BB9,BD9:BH9,B12,D12:AG12,AK12:BB12,BD12:BH12,B15,D15:AG15,AK15:BB15,BD15:BH15,B18,D18:AG18,AK18:BB18,BD18:BH18,B21,D21:AG21,AK21:BB21,BD21:BH21,B24,D24:AG24,AK24:BB24,BD24:BH24,B27,D27:AG27Dates OccurringtodaytextNO
BD47:BH47,AK47:AV47,D47:AG47Cell Value=0textNO
AI4,AI7,AI10,AI13,AI16,AI19,AI22,AI25,AI28,AI31,AI34,AI37,AI40,AI43,AI46,BD4:BG4,BD7:BG7,BD10:BG10,BD13:BG13,BD16:BG16,BD19:BG19,BD22:BG22,BD25:BG25,BD28:BG28,BD31:BG31,BD34:BG34,BD37:BG37,BD40:BG40,BD43:BG43,BD46:BG46,AK46:AZ46,AK43:AZ43,AK40:AZ40Expression=IF(AND(B3>0;B4<9);TRUE;FALSE)textNO
B2:B46,D2:AI46Cell Value=0textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
COUNTIF Function and Blank Date Cells
2
Replies
12
Views
429
Joe4
Joe4
V
  • Solved
Countif, including today's date, excluding future dates
2
Replies
13
Views
304
Peter_SSs
Peter_SSs
J
  • Solved
DATEDIF formula with TODAY's date
Replies
4
Views
177
Alex Blakenburg
A
D
  • Question
Today's Date
Replies
4
Views
110
ddd2009
D
M
  • Question
Dynamic Update to Range in RANK Based on Date
Replies
0
Views
257
multifidus
M

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top