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

Nicolette Marinos

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

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!