Countif date less than or blank

[Wishmaster89]

Hi All

I know how to do the date less than(10/12/2021) part but how do you add the if blank part please?

Book4
	A	B
1	ID	Date
2	1	01/08/2020
3	2	01/09/2020
4	3	01/02/2021
5	4	10/07/2023
6	5	07/05/2024
7	6	11/01/2019
8	7
9	8
10	9
11	10
Sheet1

 

[ExceLoki]

Book1
	A	B	C	D
1	ID	Date		count if <10/12/2021 or blank
2	1	8/1/2020		8
3	2	9/1/2020		8
4	3	2/1/2021
5	4	7/10/2023
6	5	5/7/2024
7	6	1/11/2019
8	7
9	8
10	9
11	10
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(B2:B11,"<10/12/2021")+COUNTIF(B2:B11,"")
D3	D3	=COUNTIF(B2:B11,"<10/12/2021")+COUNTBLANK(B2:B11)

 

[Wishmaster89]

Book1
	A	B	C	D
1	ID	Date		count if <10/12/2021 or blank
2	1	8/1/2020		8
3	2	9/1/2020		8
4	3	2/1/2021
5	4	7/10/2023
6	5	5/7/2024
7	6	1/11/2019
8	7
9	8
10	9
11	10
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(B2:B11,"<10/12/2021")+COUNTIF(B2:B11,"")
D3	D3	=COUNTIF(B2:B11,"<10/12/2021")+COUNTBLANK(B2:B11)

Thanks but i should have been clearer. The blanks bit needs to be for when the date is less than 10/12/2021, not just total number of blanks in column if that makes sense?

 

[Wishmaster89]

This is a better example of what i mean. I need to count the blank cells in column C as well though and thats where i am stuck.

Not sure why the date format has changed..

Book1
	A	B	C	D	E
1	ID	Planned end	Predicted exam date		Count of learners with planned end date before 13/03/2021
2	1	3/1/21	4/15/21		2
3	2	4/1/21	5/16/21
4	3	6/25/21	8/9/21
5	4	7/29/21	9/12/21
6	5	9/10/19	10/25/19
7	6	3/13/21	4/27/21
8	11	8/14/20	9/28/20
9	7	8/9/21
10	8	1/10/20
11	9	9/17/21
12	10	2/13/20
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(B2:B12,"<13/03/2021",C2:C12,"<13/03/2021")
C2:C8	C2	=B2+45

 

[ExceLoki]

similar solution to what i put before, try this:
--------------------------

Book1
	A	B	C	D	E
1	ID	Planned end	Predicted exam date		Count of learners with planned end date before 3/13/2021
2	1	3/1/2021	4/15/2021		4
3	2	4/1/2021	5/16/2021
4	3	6/25/2021	8/9/2021
5	4	7/29/2021	9/12/2021
6	5	9/10/2019	10/25/2019
7	6	3/13/2021	4/27/2021
8	11	8/14/2020	9/28/2020
9	7	8/9/2021
10	8	1/10/2020
11	9	9/17/2021
12	10	2/13/2020
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(B2:B12,"<3/13/2021",C2:C12,"<3/13/2021")+COUNTIFS(B2:B12,"<3/13/2021",C2:C12,"")
C2:C8	C2	=B2+45

 

[Wishmaster89]

Thank you Loki

 

[ExceLoki]

np, glad to help