[Excel 2013] Count cells in a data table which are empty OR greater than today without adding countif(s)

[pkdpkd]

Good morning,
I am trying to invent a formula, that in a single calculation (withoutadding two calculations) will allow me to count all "currentemployees" from "employees" table. I have a data table"Employees" with a column "Work To" which is:

• usually empty (for current employees not on leave)
• sometimes contains values in the past (employees already gone)
• and sometimes values in the future (employees on leave).

I constructed a formula: =IF(ISBLANK(Employees[WorkTo]);TODAY();Employees[Work To]) which successfully returns either [Work To]value or today's date. If I make this formula part of the data table (let'scall it [alt_Work To]), I can now successfully run: =COUNTIF(Employees[alt_WorkTo];">="&TODAY()) which gives me the proper result.

Question: how can I do it without adding an additional column? I tried:
=COUNTIF(IF(ISBLANK(Employees[Work To]);TODAY();Employees[WorkTo]);">="&TODAY())
... but this only gives me employees on leave for reasons I can'tunderstand.

Of course I understand I can just add to COUNTIFs, that's fine. But I feel there must be a way to do it in a single countif. My way of thinking in influenced by NVL function in SQL@Oracle which returns a column value or a different value if NULL. I'm trying to simulate that, actually.

If someone can help me based on this description it would be greatlyappreciated. Thanks!
PKD

 

[JonHaywood]

Hi PKD,

You could try this array formula - just substitute your actual cells reference where it says WORKTO...

=SUM(N(WORKTO="")+N(WORKTO>=TODAY()))

Enter the formula with Ctrl+shift+enter

It gives you the sum of the array results if your Work to date is blank or is not less than today.

Hope it helps

Jon