Count number of items overdue in a column depending on values in two other columns

[Groovy Chick]

Hi, I am trying to work out how to write a formula for counting number of rows in column S that are overdue in a table if the value in another column is Open and the other column is blank. So, I have a due date field in Column S and I want to count these if the date is less than today but only if column U is blank and column Z value is "Open" in the dropdown. Can anyone help me? Very many thanks.

 

[etaf]

you could use a countifs()

you dont indicate what version of excel you have

=countifs( S:S, "<"&today() , U:U , "", Z:Z, "open" )

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	2																		1/25/22		1					open
2																			1/25/22		2
3																			1/24/22		3					open
4																			1/24/22		4					open
5																			1/24/22		5
6																			1/24/22		6					open
7																			1/23/22		7					open
8																			1/23/22		8
9																			1/23/22							open
10																			1/23/22							open
11																			1/22/22		11
12																			1/22/22		12					open
Sheet1

Cell Formulas
Range	Formula
A1	A1	=COUNTIFS( S:S, "<"&TODAY(), U:U, "", Z:Z, "open" )
S1:S2	S1	=TODAY()
S3:S6	S3	=TODAY()-1
S7:S10	S7	=TODAY()-2
S11:S12	S11	=TODAY()-3

 

[Toadstool]

Hi Groovey Chick,

Is this what you want?

GroovyChick.xlsx
	S	T	U	V	W	X	Y	Z
1	Date		Column U		Result			Status
2	01-Jan-22		Dog		3			Open
3	03-Jan-22							Open
4	09-Jan-22		Cat					Closed
5	22-Jan-22		Sheep					Open
6	23-Jan-22							Closed
7	24-Jan-22							Open
8	25-Jan-22							Closed
9	22-Jan-22							Open
10	27-Jan-22							Closed
11	02-Feb-22							Open
12
Sheet1

Cell Formulas
Range	Formula
W2	W2	=COUNTIFS($S$2:$S$9999,"<"&TODAY(),$U$2:$U$9999,"",$Z$2:$Z$9999,"Open")

 

[Groovy Chick]

Thank you so much to you both. Works a treat! I was nearly there with the countifs example but was over complicating it. I really appreciate the help. I'm on a steep learning curve with formulas and this forum is invaluable. Hopefully, over time, I will improve and maybe able to answer some basic queries to contribute to the site rather than always asking for help! Many thanks again.

 

[etaf]

you are welcome