Countif, including today's date, excluding future dates

[valmir]

Hello everyone
I'm using this formula:
=COUNTIF(D3:AG3;">"&DATE(1900;1;1))
And the reason I'm using this particular formula is that within the "D3:AG3" range, I have cells with formulas whose result is not a date and/or blank cells.
I understand that this formula will count only those cells with results as date.
Now I would like this formula to be modified so as to exclude any future date but to include today's date
Thanks a lot!

 

[Peter_SSs]

I understand that this formula will count only those cells with results as date.

That is not strictly true, but it may be for your particular data. For example, here is that formula working on a smaller range and it has counted more than just the one date cell.
It has counted D3, E3 and I3

24 02 19.xlsm
	C	D	E	F	G	H	I	J
3	3	15/06/2023	2.36	a	-32	6666a	1.732051
Countif

Cell Formulas
Range	Formula
C3	C3	=COUNTIF(D3:J3,">"&DATE(1900,1,1))
I3	I3	=SQRT(3)

So before we move on to your actual question, can you clarify whether this is an issue for you or not?

 

[valmir]

That is not strictly true, but it may be for your particular data. For example, here is that formula working on a smaller range and it has counted more than just the one date cell.
It has counted D3, E3 and I3

24 02 19.xlsm
	C	D	E	F	G	H	I	J
3	3	15/06/2023	2.36	a	-32	6666a	1.732051
Countif

Cell Formulas
Range	Formula
C3	C3	=COUNTIF(D3:J3,">"&DATE(1900,1,1))
I3	I3	=SQRT(3)

So before we move on to your actual question, can you clarify whether this is an issue for you or not?

Thanks for your prompt reply. In my case, this formula: =COUNTIF(D3:AG3;">"&DATE(1900;1;1)) is ignoring all cells without dates, unlike this formula, for example: =COUNTIF(D3:AG3;"<"&TODAY()) which counts all cells within the range except the ones with future dates! So, any formula is fine as long as it ignores all cells without dates

 

[Peter_SSs]

Could we see a sample of your data with XL2BB so that we can see what formulas you are using in the cells and what sort of values they are producing?

 

[valmir]

That is not strictly true, but it may be for your particular data. For example, here is that formula working on a smaller range and it has counted more than just the one date cell.
It has counted D3, E3 and I3

24 02 19.xlsm
	C	D	E	F	G	H	I	J
3	3	15/06/2023	2.36	a	-32	6666a	1.732051
Countif

Cell Formulas
Range	Formula
C3	C3	=COUNTIF(D3:J3,">"&DATE(1900,1,1))
I3	I3	=SQRT(3)

So before we move on to your actual question, can you clarify whether this is an issue for you or not?

Just to clarify, there are no blank cells within that range. All cells with formulas, some returning dates, others returning 0

 

[valmir]

Stats 2023-24.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
2		UML	KAB	SAG	DHL	PET	WIL	DLS	BMQ	RCL	PRI	INT	SRC	SCC	SSC		UML	KAB	SAG	DHL	PET	WIL	DLS	BMQ	RCL	PRI	INT	SRC	SCC	SSC		10
3		4/11	11/11	17/11	22/11	6/3		13/2	14/12	29/12		10/2	17/2																		9	-1
4		11,5	11	11,5	11,4			11,5	11,5	11		11,4	11,2
APPS

Cell Formulas
Range	Formula
D2	D2	=INDIRECT($A3&"!$GK$5")
E2	E2	=INDIRECT($A3&"!$GK$6")
F2	F2	=INDIRECT($A3&"!$GK$7")
G2	G2	=INDIRECT($A3&"!$GK$8")
H2	H2	=INDIRECT($A3&"!$GK$9")
I2	I2	=INDIRECT($A3&"!$GK$10")
J2	J2	=INDIRECT($A3&"!$GK$11")
K2	K2	=INDIRECT($A3&"!$GK$12")
L2	L2	=INDIRECT($A3&"!$GK$13")
M2	M2	=INDIRECT($A3&"!$GK$14")
N2	N2	=INDIRECT($A3&"!$GK$15")
O2	O2	=INDIRECT($A3&"!$GK$16")
P2	P2	=INDIRECT($A3&"!$GK$17")
Q2	Q2	=INDIRECT($A3&"!$GK$18")
R2	R2	=INDIRECT($A3&"!$GK$19")
S2	S2	=INDIRECT($A3&"!$GK$20")
T2	T2	=INDIRECT($A3&"!$GK$21")
U2	U2	=INDIRECT($A3&"!$GK$22")
V2	V2	=INDIRECT($A3&"!$GK$23")
W2	W2	=INDIRECT($A3&"!$GK$24")
X2	X2	=INDIRECT($A3&"!$GK$25")
Y2	Y2	=INDIRECT($A3&"!$GK$26")
Z2	Z2	=INDIRECT($A3&"!$GK$27")
AA2	AA2	=INDIRECT($A3&"!$GK$28")
AB2	AB2	=INDIRECT($A3&"!$GK$29")
AC2	AC2	=INDIRECT($A3&"!$GK$30")
AD2	AD2	=INDIRECT($A3&"!$GK$31")
AE2	AE2	=INDIRECT($A3&"!$GK$32")
AF2	AF2	=INDIRECT($A3&"!$GK$33")
AG2	AG2	=INDIRECT($A3&"!$GK$34")
AI2	AI2	=COUNTIF(D3:AG3,">"&DATE(1900,1,1))
D3	D3	=INDIRECT($A3&"!$GI$5")
E3	E3	=INDIRECT($A3&"!$GI$6")
F3	F3	=INDIRECT($A3&"!$GI$7")
G3	G3	=INDIRECT($A3&"!$GI$8")
H3	H3	=INDIRECT($A3&"!$GI$9")
I3	I3	=INDIRECT($A3&"!$GI$10")
J3	J3	=INDIRECT($A3&"!$GI$11")
K3	K3	=INDIRECT($A3&"!$GI$12")
L3	L3	=INDIRECT($A3&"!$GI$13")
M3	M3	=INDIRECT($A3&"!$GI$14")
N3	N3	=INDIRECT($A3&"!$GI$15")
O3	O3	=INDIRECT($A3&"!$GI$16")
P3	P3	=INDIRECT($A3&"!$GI$17")
Q3	Q3	=INDIRECT($A3&"!$GI$18")
R3	R3	=INDIRECT($A3&"!$GI$19")
S3	S3	=INDIRECT($A3&"!$GI$20")
T3	T3	=INDIRECT($A3&"!$GI$21")
U3	U3	=INDIRECT($A3&"!$GI$22")
V3	V3	=INDIRECT($A3&"!$GI$23")
W3	W3	=INDIRECT($A3&"!$GI$24")
X3	X3	=INDIRECT($A3&"!$GI$25")
Y3	Y3	=INDIRECT($A3&"!$GI$26")
Z3	Z3	=INDIRECT($A3&"!$GI$27")
AA3	AA3	=INDIRECT($A3&"!$GI$28")
AB3	AB3	=INDIRECT($A3&"!$GI$29")
AC3	AC3	=INDIRECT($A3&"!$GI$30")
AD3	AD3	=INDIRECT($A3&"!$GI$31")
AE3	AE3	=INDIRECT($A3&"!$GI$32")
AF3	AF3	=INDIRECT($A3&"!$GI$33")
AG3	AG3	=INDIRECT($A3&"!$GI$34")
AH3	AH3	=COUNTIFS(D4:AG4,">0",D4:AG4,"<=1000")
AI3	AI3	=AH3-AI2
D4:AG4	D4	=IFERROR(VALUE(INDEX(INDIRECT($A3&"!$R$2:$FK$51"),50,MATCH(D1,INDIRECT($A3&"!$R$2:$FK$2"),0))) + VALUE(INDEX(INDIRECT($A3&"!$R$2:$FK$51"),50,MATCH(D1,INDIRECT($A3&"!$R$2:$FK$2"),0)+1))/10,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
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:AZ40	Expression	=IF(AND(B3>0;B4<9);TRUE;FALSE)	text	NO
AI3	Cell Value	<0	text	NO
B2:B46,D2:AI46	Cell Value	=0	text	NO

 

[valmir]

The result in AI2 should be 9 instead of 10

 

[Peter_SSs]

The result in AI2 should be 9 instead of 10

The formula in AI2 is =COUNTIF(D3:AG3,">"&DATE(1900,1,1))
That formula is counting values in row 3 from column D to AG. In that range there are dates in E3, F3, G3, H3, I3, K3, L3, M3, O3, P3
That is 10 dates. So why should it return 9 instead of 10?

 

[valmir]

The formula in AI2 is =COUNTIF(D3:AG3,">"&DATE(1900,1,1))
That formula is counting values in row 3 from column D to AG. In that range there are dates in E3, F3, G3, H3, I3, K3, L3, M3, O3, P3
That is 10 dates. So why should it return 9 instead of 10?

Because one of them is a future date...

 

[Peter_SSs]

Because one of them is a future date...

Which one? None of them show a year so impossible to tell from looking at them. Can you format row 3 with a date format that shows day, month and year and post it again with XL2BB?