0

I need to create a formula that counts the number of times there is an overdue date in column I, where the corresponding cell in column J is blank.

I enter a date in column H (date appointed). There is a formula to auto-fill column I (date due) which is:

=IF(ISBLANK(H6), " ", EDATE(H6,6))

I need to create a formula to count the number of dates in column I which are older than today but only if the corresponding cell in column J is blank.

I have tried:

=COUNTIFS(I6:I260,"<TODAY()",J6:J260,"<>"&"""")

but I suspect it is counting the auto-fill formula in column I.

  • Wait — do you want to count the rows where Column J is blank, or where it isn’t? – Scott Feb 19 '19 at 5:22
0

enter image description here

Use this Array (CSE) formula, finish with Ctrl+Shift+Enter.

{=SUMPRODUCT(COUNTIFS($I$139:$I$145,IF({1;0},"","<"&TODAY()),$J$139:$J$145,""))}

N.B.

  • IF function generates an array that resolves to something like this, {"";">43510"}.
  • Adjust cell references in the formula as needed.
  • 1
    This works, but I'm curious why you didn't use the simpler formula: =COUNTIFS($I$139:$I$145,"<"&TODAY(),$J$139:$J$145,"") (non-array) – fixer1234 Feb 14 '19 at 8:10
  • 1
    @fixer1234,, yeah this also works,, but while I was testing sometimes returns 7 so that I've wrapped with array ☺ – Rajesh S Feb 14 '19 at 8:44
  • Thank you everyone they were both really helpful examples. I really appreciate your assistance and time taken to help me. cheers – Jevene Feb 14 '19 at 22:39
  • @Jevene,, glad to help you ,, keep asking ☺ – Rajesh S Feb 15 '19 at 9:28
0

You’re making two mistakes:

  • When you say "<TODAY()", you’re comparing the value (in Column I) to the string TODAY().  So, for example, if you put the days of the week into Column I as strings, then Monday, Thursday, Friday, Saturday and Sunday will be seen as being <TODAY(), but Tuesday and Wednesday will not.  However, no actual date values will be treated as being <TODAY().

    • Normally (e.g., if you do IF(D1<S1,…)), all dates are considered to be less than all strings.  However, for purposes of COUNTIF, dates are not considered to be less than, greater than, or equal to strings.  They just aren’t counted.   This does not appear to be documented.
    • The fact that the values in Column I are computed by a formula is not an issue.

    You need to say "<"&TODAY() to get the value of today’s date.

  • When you have two double quotes in a row in a string constant, they act like one double quote character.  That’s confusing.  For example, if you say "foo""bar" in a formula, it evaluates to the string value foo"bar.  By the same rule, if you say """" in a formula, it evaluates to the string value ", and when you say "<>"&"""", it evaluates to <>", so you are comparing the values in Column J to ".

    To check for values not being blank, you need to say "<>"&"", or simply "<>", which is terribly non-intuitive.

    • slightly clearer syntax is =*, which tests whether the value is a non-null string.  However, <> will count all cells that are not empty, while =* will not count cells that contain numbers or dates.  Since you don’t say what you have in Column J, I don’t know whether this is useful to you.

So the formula you want is

=COUNTIFS(I6:I20,"<"&TODAY(), J6:J20,"<>")

Note that this is not an array formula, so it does not require Ctrl+Shift+Enter.

For example:

        spreadsheet snippet demonstrating the above formula

  • Row 6 counts because I6 (February 11, 2019) is less than today (February 19, 2019) and J6 (foo) is not blank.
  • Rows 7-9 don’t count because they are blank in Column J.
  • Row 10 doesn’t count because I10 is blank.  (Ditto for Rows 15-20.)
  • Rows 11 and 12 don’t count because I11 (February 25, 2019) and I12 (February 26, 2019) are greater than today’s date.
  • Rows 13 and 14 because the dates in Column I are in the future and they are blank in Column J.
  • thanks Scott, appreciate your in-depth answer. It does help explain everything. Still new to learning excel formulas but love a challenge and am impressed by you all for your vast knowledge. – Jevene Feb 20 '19 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.