How to use countifs statement

[suet]

Hi All

Hope you are all well.

I am trying to work out a formula that will count cells in 2 different columns

I have different dates in the range K7:K79

I have a different statuses in the range L7L79

I need a formula to work out all the dates in the range K7:k79 that are less then today and that also have the status Open in the range L7:L79.

I have tried this formula but it keeps giving me the total amount of rows: =COUNTIFS(K7:K79,"<"&TODAY()+COUNTIFS(L7:L79,"Open"))

Todays date is 17/03/2023. I have 4 rows of information whereby the Due date is before today so need to return the result of 4 but my formula above gives me the total number of rows.

Column K Column L
Due Date Status
24/03/2023 Open
17/03/2023 Closed
14/03/2023 Open
15/03/2023 Open
28/03/2023 Open
19/04/2023 Open
24/01/2023 Open
02/03/2023 Open

Any help would be greatly appreciated.

Regards
Sue

 

[scoudny]

Hi Suet, try
=COUNTIFS(K7:K79,"<"&TODAY(),L7:L79,"Open")

 

[suet]

=COUNTIFS(K7:K79,"<"&TODAY(),L7:L79,"Open")

Hiya

That is perfect!!! Thank you very much. Have a good day and weekend.