# Formula to count if 2 conditions occur

1. ## Formula to count if 2 conditions occur

I have a sheet with 2 columns of dates "Date opened" and "Date closed"

not all lines have a date closed entry.

I'm trying to count how many cases have been opened longer than 18 months and are still open.

=COUNTIF('Tracker'!F6:F1582,"<"&TODAY()-"545")

The above gives me the count of cases with date opened older than 545 days ago (18 months approx).
If I use filters I can see that there are 8 cases of which 3 are still open (ie don't have a date in the closed column)

How do I get the formula to count the blanks or subtract the non-blanks from the new count?

2. ## Re: Formula to count if 2 conditions occur

Hello Stufft,

Welcome to the forum

Available from Excel 2007 onward is COUNTIFS, where it's COUNTIF with more condition to it, for example

=COUNTIFS('Tracker'!F6:F1582, "<"&TODAY()-"545", Range 2, "<>""")

Where it will count the number of date opened older than 545 days ago, and the date in Range 2 is not blank.

Available for all Excel version is SUMPRODUCT, you can use it like a COUNTIFS on any version

many thanks

4. ## Re: Formula to count if 2 conditions occur

Originally Posted by Lemice
=COUNTIFS('Tracker'!F6:F1582, "<"&TODAY()-"545", Range 2, "<>""")
No need to quote 545

Is not blank can be expressed as <>.

=COUNTIFS('Tracker'!F6:F1582,"<"&TODAY()-545,Range2,"<>")

5. ## Re: Formula to count if 2 conditions occur

FYI, the SUMPRODUCT function works if there's another column counting the number of weeks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1