Using CountIfs to check if cells have dates or are empty

Question

I have written a formula to check if a small range has a date in the cells. My data looks like this:

+----------+------------+
| Proposed | Dates Used |
+----------+------------+
|        0 | 9/23/2019  |
|        0 | 9/24/2019  |
|        0 | 9/25/2019  |
|        0 | 9/26/2019  |
|        0 | 9/27/2019  |
|        0 | 9/29/2019  |
|        0 | 9/30/2019  |
|        1 | 10/2/2019  |
|        1 |            |
|        0 |            |
+----------+------------+

My current formula looks like this: =COUNTIFS(Propsed_Days,0,Dates_Used,"<>0") This returns an error. I have also tried =COUNTIFS(Propsed_Days,0, Dates_Used,"<>"), which also returns an error. (I am using defined names to cover the ranges, right now Propsed_Days covers both columns and Dates_Used covers just the date column)

Basically what I want is to get a count of dates that have the Proposed column = 0, but don't want to count when there is no date but proposed is = 0. My table above should = 7 because there are 7 dates that have a proposed = 0 the date with the proposed = 1 should be excluded and the 2 lines (one with Proposed = 0 and one with Proposed = 1) should be excluded because there are no dates in the Dates Used column.

I have tried searching for a solution and tried answers from Using COUNTIFS to count blank when cell has a formula and How do I get countifs to select all non-blank cells in Excel?, but neither questions answers have worked for me.

Terry W Terry W · Accepted Answer · 2019-08-29T00:49:20

The following formula will work ONLY IF the last two cells in your Dates_Used column is completely blank but not a blank returned by a formula.

=COUNTIFS(Propsed_Days,0,Dates_Used,"<>")

I suspect the dates in your Dates_Used column are returned by a formula so the blank cells are not really blank. If that's the case, the following SUMPRODUCT formula will do the job:

=SUMPRODUCT((Propsed_Days=0)*(LEN(Dates_Used)>0))

Cheers :)

Using CountIfs to check if cells have dates or are empty

2 Answers