0

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.

Mike
  • 1,853
  • 3
  • 45
  • 75

2 Answers2

1

The named range "Proposed Days" will cause the issue since this is for both columns, you need to adjust this to only cover the first column.

Once you've done that you need to concatenate the condition like so.

=COUNTIFS(Proposed_Days,0,Dates_Used,"<>"&0)

Mark S.
  • 1,474
  • 1
  • 6
  • 20
  • I have tried this and it did not work. I adjusted the `Proposed_Days` to only be the Proposed column and used the `concat` in the formula exactly as you have it. – Mike Aug 29 '19 at 15:02
  • @Mike I fixed the formula, I adjusted the one you had in your question which had a typing error in it. Try it again. – Mark S. Aug 29 '19 at 15:06
  • This still doesn't work. I still get the same error `#VALUE!` – Mike Aug 29 '19 at 15:08
  • 1
    Make sure those named ranges are the same size, they can't vary. If one range is longer than the other this will produce an `#VALUE!` error. – Mark S. Aug 29 '19 at 15:14
  • Holy Crap!! That was it! The ranges needed to be the same size and one was longer by 1 cell. Your formula still didn't work it counted too many still, but my original formula does now work. `=COUNTIFS(Proposed_Days,0,Dates_Used,"<>")` – Mike Aug 29 '19 at 15:23
1

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))

Solution

Cheers :)

Terry W
  • 3,199
  • 2
  • 8
  • 24
  • This did not work for me. I tried using the `sumproduct` exactly as you have it and it did not work I get the `#N/A` error. Also, the dates are manually typed in and not the result of a formula. That is why I don't understand why this isn't working. – Mike Aug 29 '19 at 15:07
  • I had the named ranges different sizes, that is why it wasn't working. I fixed that and now my formula is working as: `=COUNTIFS(Proposed_Days,0,Dates_Used,"<>")` – Mike Aug 29 '19 at 15:24