Count all projects in a year with a blank


I am trying to count all projects completed in a certain year (ex. 2021), that have a blank Completion Date.

I am using the following with no success: =COUNTIFS({Tracker (BAU) Range 3}, ISBLANK(@cell), {Tracker (BAU-VBuild) Range 2}, IFERROR(YEAR(@cell), 0) = 2021)

Any and all help is welcome, Thank You.


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Alexi Resendiz

    Hope you are fine, please try the following formula ( i create it in the same sheet because i don't know what is your range reference, and you can convert it to refer to another sheet)

    =COUNTIFS(Status:Status, "Completed", [Completion Date]:[Completion Date], AND(ISDATE(@cell), IFERROR(YEAR(@cell), "") = 2021))

    the following screenshot shows the result:

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hello,

    I am not counting cells that have writing.

    I am referencing a second sheet, and need to count all projects in 2021, that have a blank cell in under the completion tab.

  • ✭✭✭✭✭

    @Alexi Resendiz... Could you provide a screen capture

    It could be as simple as =COUNTIFS([Project Year]:[Project Year], 2021, Status:Status, "")

    I do not know the format of your date.. So may need to extract the year like @Bassam Khalil did.

    1. Note that the COUNTIFS can Reference other sheets as well.. Just choose the ranges correctly
    2. The COUNTIFS equation implies an AND scenario.. So Project in 2021 AND Status is Blank

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!