Sheet Formula

Happy 2024 everyone, I hope your year is off to a great start! I'm back requesting help to get some formulas working. I am trying to use the countif formula to count a column in a sheet. I am trying to get it to count all the dates in the column, but I also want to use to the formula to count all the rows with that same column where there is no date. In other word I want to count all of my projects that have a completion date, and then count all my projects that do not. Lastly I want to use the countif formula to count all my projects year to date. Can someone assist with this?

Best Answer

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @Nick Wilson ,

    I believe the three formulas you need are as follows:

    1. All the rows where there is a date - =COUNTIF([DateColumn]:[DateColumn],@cell<>"")
    2. All the rows where there is not a date - =COUNTIF([DateColumn]:[DateColumn],@cell="")
    3. All the rows where the date is this year - =COUNTIFS([Date Column]:[Date Column], @cell <> "", [Date Column]:[Date Column], YEAR(@cell) = YEAR(TODAY()))

    It is worth noting that the second formula as is will return all blank rows at the end of a sheet so using the formula below referencing a cell which should always have a value as per the formula below.

    =COUNTIFS([DateColumn]:[DateColumn], @cell = "", [OtherColumn]:[OtherColumn], @cell <> "")

    Hope this helps!

    John

Answers

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @Nick Wilson ,

    I believe the three formulas you need are as follows:

    1. All the rows where there is a date - =COUNTIF([DateColumn]:[DateColumn],@cell<>"")
    2. All the rows where there is not a date - =COUNTIF([DateColumn]:[DateColumn],@cell="")
    3. All the rows where the date is this year - =COUNTIFS([Date Column]:[Date Column], @cell <> "", [Date Column]:[Date Column], YEAR(@cell) = YEAR(TODAY()))

    It is worth noting that the second formula as is will return all blank rows at the end of a sheet so using the formula below referencing a cell which should always have a value as per the formula below.

    =COUNTIFS([DateColumn]:[DateColumn], @cell = "", [OtherColumn]:[OtherColumn], @cell <> "")

    Hope this helps!

    John

  • That did the trick! THX!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!