Counting rows that contain specific years

RebTrachs

I'm trying to create a formula in my sheet summary that will count the number or projects that contain a specific year in a column that shows the start date for the project. I have a task list spreadsheet and it contains tasks that started in multiple years but I'd like to be able to show metrics on our dashboard for how many projects we started in each year. It currently only counts the total number of projects on the sheet.

Emilio Wright

@RebTrachs This should do it. You need the IFERROR() to catch any blank cells since we are using a range and the @cell value.

=COUNTIF([Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2023)

Emilio Wright

@RebTrachs Okay. Thank you for the clarification. I always have VS Code open because I use the Smartsheet API, so I tend to always create my formulas there because I can clearly see column names and it has Bracket Pairing. I haven't tested this on a sheet, but this should work.

=if(isblank([Actual Completion Date]@row),if([Estimated Completion Date]@row < TODAY(),"Late",""),IF([Actual Completion Date]@row > [Estimated Completion Date]@row,"Late",IF([Actual Completion Date]@row = [Estimated Completion Date]@row,"On Time","Early")))

Emilio Wright

@RebTrachs This should do it. You need the IFERROR() to catch any blank cells since we are using a range and the @cell value.

=COUNTIF([Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2023)

DKazatsky

Hi @RebTrachs

Create a helper column called "Start Date Year" with this column formula: =IFERROR(YEAR([Start Date]@row), "")

For the summary field use this: =COUNTIF([Start Date Year]:[Start Date Year], =2023)

Substitute correct year you need

Hope this helps,

Dave

RebTrachs

Thank you! That worked perfectly!

Can you help me with another formula? I found this one in a template and it works for most cases but it marks any rows without an Actual Completion Date filled in as "Early" if the Estimated Completion Date is in the past. I would prefer that it mark that cell as "Late" instead. I've tried to add to this but am maybe not getting the parentheses correct to complete the formula so it errors out.

=IF(AND(ISBLANK([Actual Completion Date]@row), [Estimated Completion Date]@row > TODAY()), "", IF([Actual Completion Date]@row > [Estimated Completion Date]@row, "Late", IF([Actual Completion Date]@row = [Estimated Completion Date]@row, "On Time", IF([Actual Completion Date]@row < [Estimated Completion Date]@row, "Early"))))

Emilio Wright

@DKazatsky You can nest the "helper column" inside the formula on your Sheet Summary and avoid extra stuff.

RebTrachs

@Emilio Wright

Can you help me with another formula? I found this one in a template and it works for most cases but it marks any rows without an Actual Completion Date filled in as "Early" if the Estimated Completion Date is in the past. I would prefer that it mark that cell as "Late" instead. I've tried to add to this but am maybe not getting the parentheses correct to complete the formula so it errors out.

=IF(AND(ISBLANK([Actual Completion Date]@row), [Estimated Completion Date]@row > TODAY()), "", IF([Actual Completion Date]@row > [Estimated Completion Date]@row, "Late", IF([Actual Completion Date]@row = [Estimated Completion Date]@row, "On Time", IF([Actual Completion Date]@row < [Estimated Completion Date]@row, "Early"))))

Emilio Wright

@RebTrachs Are you able to explain it again as I am not sure I follow what you meant. As I read it, this is what I got:

• mark cells "Late" IF Actual Completion Date equals "Early" and IF Estimated Completion Date is < Today?

What should the other options be? I see Blank, On Time and Late as well.

RebTrachs

@Emilio Wright So the formula works as is for the ones listed but I need to add a function that would mark anything with an Estimated Completion Date that's in the past but the Actual Completion Date is blank. Right now, if there is no Actual Completion Date and the date the person listed as the estimated completion date passes, it will mark the task as having been done early but it's actually not complete at all and has passed the expected date so should be late.

Emilio Wright

@RebTrachs Okay. Thank you for the clarification. I always have VS Code open because I use the Smartsheet API, so I tend to always create my formulas there because I can clearly see column names and it has Bracket Pairing. I haven't tested this on a sheet, but this should work.

=if(isblank([Actual Completion Date]@row),if([Estimated Completion Date]@row < TODAY(),"Late",""),IF([Actual Completion Date]@row > [Estimated Completion Date]@row,"Late",IF([Actual Completion Date]@row = [Estimated Completion Date]@row,"On Time","Early")))

RebTrachs

@Emilio Wright Thank you!!! That worked perfectly! I really appreciate the help with these formulas!