How to count number of projects in 2023 by current status

SamanthaHopeDuignam

Hi,

I created a Metrics sheet that feeds a dashboard last year that counted the status of a variety of items and it worked great when there was just one year. This year, I now need to only count the items that are for 2023. In the main sheet, I have a column that has the year in a string format (it's a formula =RIGHT([Planned Delivery]@row, 4)) where Planned Delivery is a picklist of MMM YYYY.

My Project tracking sheet

My Metrics sheet

My original formula that works for counting all the projects:

=COUNT(COLLECT({Project ID}, {Project Status}, $Label@row))

What I've tried:

=COUNTIFS(COLLECT({Project ID}, {BOW Year}, "2023", {Project Status}, $Label@row,)) Result is #UNPARSEABLE

=COUNTIF(COLLECT({Project ID}, {Project Status}, $Label@row), CONTAINS("2023", {BOW Year})) Result is 0

=COUNTIF(COLLECT({Project ID}, {Project Status}, $Label@row, {BOW Year}, "2023"), 1) Result is 0

Can someone please help me figure out what I need to do to update my metrics sheet to only include items flagged as 2023? Thanks!

Eric Law

@SamanthaHopeDuignam This is a simple solution =COUNT(COLLECT({Project ID}, {Project Status}, $Label@row,{BOW Year}, =2023))

SamanthaHopeDuignam

Thanks @Eric Law. I tried it, and it's returning 0 as the result... any thoughts?

Eric Law

@SamanthaHopeDuignam Try swapping out =2023 for "2023". I looks like your BOW Year is a string not an integer.

SamanthaHopeDuignam

@Eric Law still results with 0 😕 I checked the ranges, they are correct.

Eric Law

@SamanthaHopeDuignam Could you change the BOW Year to, instead of a string, be =YEAR([Planned Delivery]@row) and use the first formula I posted?

SamanthaHopeDuignam

This worked - thanks @Eric Law!