Countif Columns - that have dates then subtract

Options
Elizabeth Aird
Elizabeth Aird ✭✭✭✭
edited 02/07/22 in Formulas and Functions

I have 3 columns that may or may not contain dates.

I want to count if they are blank.

Take that number and subtract from 3.


Once an employee hits their 90 days working there is a formula to enter the 3 at that time which why you see the fx there.



Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Elizabeth Aird

    Are you trying to determine Sick Days Used, or Sick Days Available (from 3)? Sick Days Used can be counted directly using the formula below.

    =COUNTIFS([Sick Day 1]@row:[Sick Day 3]@row, ISDATE(@cell))

    Sick Days Available

    =3-COUNTIFS([Sick Day 1]@row:[Sick Day 3]@row, ISDATE(@cell))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Elizabeth Aird

    Are you trying to determine Sick Days Used, or Sick Days Available (from 3)? Sick Days Used can be counted directly using the formula below.

    =COUNTIFS([Sick Day 1]@row:[Sick Day 3]@row, ISDATE(@cell))

    Sick Days Available

    =3-COUNTIFS([Sick Day 1]@row:[Sick Day 3]@row, ISDATE(@cell))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!