How to Count a column with a specific project type that falls on a specific week.

I am trying to count how many dates within a certain column that are identified by a type in another column fall every week of the year.

As a further example, I have 1 column that's a project type and another with dates.

On another sheet, I'd like to count how many projects for "Project Type A" fall on week 28 of the year, and then week 29, 30, etc.

I started to create a metric sheet to gather the info that looked something like this, but I couldn't quite figure out the formula.

The added obstacle is that ultimately, I have about 10 teams that I would then need to aggregate all their information and find out how many projects A types are in stage 1 during week 28.

All suggestions and help are appreciated

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Nicolette Marinos

    I hope you're well and safe!

    Try something like this. (ensure that you format the Week with two digits (01, 02, and so on)

    =COUNTIFS({Date}, IFERROR(WEEKNUMBER(@cell), 0) = RIGHT([Project Type A]@row, 2), {Date}, IFERROR(YEAR(@cell), 0) = 2022, {Project Type}, "Project Type A")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!