Welcome!

Count all projects in a year with a blank

Hello,

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.

Find more posts tagged with

Comments

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:

Alexi Resendiz

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.

bcwilson.ca

@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.

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

Sign in to join the conversation!

Quick Links

Trending discussions

Formula to check a box based on criteria
Hello , I am looking for help to write a formula that will check a box if certain criteria are met. I have students that are signing up for a class and they need the previous class or classes as prerequisites. for example i have class 1, class 2 , class 3 and class 4. each higher number class needs to have completed the…
Min Collect Help - date returning zero value
Hello, I am looking for formula help where I want to return the earliest date in a range for different workstreams groups on a project. The source sheet is formatted as a date field, and the formula used below is returning a 0 no matter what I do. Any suggestions? =MIN(COLLECT({Project Plan - start date}, {Project Plan…
Exporting Smartsheet formulas
From my research, I understand there isn't a way to keep formulas when exporting from Smartsheet into an Excel document. I have a total of 50 columns with formulas and would think there was a quicker way to grab the formulas. So far, I've appended a "!" which turns the formula into text which does export. However, I'm not…