Count if a specific date value exists in a range of two columns

I have a spreadsheet that is counting due dates by specific date. In column E, I have the original "Due Date". Column F could contain a "Revised Due Date" only if I have to revise the due date. I'm keeping them in separate columns because we don't want to lose track of what the original due date was, and we do want data that shows if we had to revise the due date.

I'm trying to come up with a formula that looks at both cells on the same row in Column E and Column F, and if either one contains a specific date, it would count it.

For example, if I'm trying to count the number of instances I have a due date of January 4, 2021, and the date of January 4, 2021 exists in Column E/line 11, and no date exists in Column F/line 11, it should be counted. However, if a different date exists in Column E/line 11 but the January 4, 2021 date exists in Column F/line 11, it should still be counted.

Right now, the formula I have is:

=COUNTIFS(Sheet1!F11,"",Sheet1!E11,DATE(2021,1,4))

In this scenario, it's counting 1 if Cell E11 has a date of January 4, 2021 and Cell F11 is empty. The logical thought I had was that where I have the criteria for empty (""), I replace it with the 1/4/2021 date instead. But then it's only yielding a count if BOTH cells contain that date, and I want it to count if EITHER cell contains that date. There will not be an instance where both cells contain the same date. So it should yield a count of 1 if either the value in column E or the value in column F is the specified date.

I cannot figure out how to write that formula. I can't figure a way to do a COUNTIFS(OR formula. Anyone know what will work?

Thanks in advance!

The formula you showed is only looking at two specific cells in one row, not ranges of cells. If you are looking for an answer only for one row you don't have to use COUNTIFS.

=IF(OR(Sheet1!F11=DATE(2021,1,4),Sheet1!E11=DATE(2021,1,4)),1,0)

Will give you 0 or 1.

I'm actually working in a range of cells so have to use COUNTIFS. Therefore, E will always have a date and it could be a date match, but F will only occasionally have a date, and if it does, I'd need it to be counted in a date match, and I'd need any matching dates in E that have a date next to them in F to not be counted.

So column E is "Due Date". Column F is "Revised Due Date". For historical purposes we don't want to delete the value in column E if a revised due date is entered next to it in Column F. So, in the formula it's looking for the date value of 1/4/2021 and counting it as an instance. However, if on the same row, I have an original due date (column E) of 1/4/2021, and there's a revised due date (which will never be the same as the original, or this cell will be empty if there's no revised due date), I want the fact that there's a date value in F on the same row to "not count" the matching date in column E. However, I want a formula that will still count instances of 1/4/2021 in Column F (revised due date).

In other words, if there's a match in column E but column F on the same row as that match has a value, don't count the match in column E. But, it's also looking for date matches in Column F and counting those.

It's like I need an IF/OR for a COUNTIFS formula where I can say, if there's a match in E with no value in F, count it. If there's a match in E but a value in F, don't count it. If there's a match in F, count it.

I see. So if column E matches 1/4/2021, but the date is revised in column F, you do not want to count the match in column E. That is very helpful.

Formula:

Please Login or Register  to view this content.

This assumes that if the matching date is found in column F, it will not be the same date as column E.

I think this could also be done with an array formula using booleans but the above certainly works.

Thank you! That solved the problem! I don't know how to mark this as solved but it is SOLVED!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.