COUNTIFS formula for year + stage columns

Kristina Todoric

Hello,

Please could someone help me with a formula to calculate all projects that have a the year 2021 in the auto created date + where the stage column = Complete

Thank you!

Genevieve P.

Hi @Kristina Todoric

Try wrapping an IFERROR statement around the YEAR function in case this is where the error is coming (for example if it's trying to evaluate an empty cell).

=COUNTIFS(Created:Created, IFERROR(YEAR(DATEONLY(@cell)), 0) =2021, Stage:Stage, "Complete")

If that hasn't helped, it would be useful to see screen captures of your sheet, but please block out sensitive data.

Cheers,

Genevieve

Kelly Moore

Hello @Kristina Todoric

This syntax is for counting within the same sheet as the data.

=COUNTIFS(Created:Created, YEAR(DATEONLY(@cell))=2021, Stage:Stage, "Complete"). You should be able to copy paste this formula into your sheet.

This syntax should be used if you are pulling this data into a separate sheet

=COUNTIFS({Source sheet Created Column}, YEAR(DATEONLY(@cell))=2021, {Source sheet Stage Column}, "Complete")

Remember when creating cross sheet references you must create each one of these references - you cannot simply copy paste into your sheet.

Does this work for you?

Kelly

Kristina Todoric

Hi Kelly,

Thanks so much for helping me.

I've tried both syntax but they return the same error message = #INVALID DATA TYPE

Would you know why this is happening?

Thanks,

Kristina

Genevieve P.

Hi @Kristina Todoric

Try wrapping an IFERROR statement around the YEAR function in case this is where the error is coming (for example if it's trying to evaluate an empty cell).

=COUNTIFS(Created:Created, IFERROR(YEAR(DATEONLY(@cell)), 0) =2021, Stage:Stage, "Complete")

If that hasn't helped, it would be useful to see screen captures of your sheet, but please block out sensitive data.

Cheers,

Genevieve

Kelly Moore

Hey Kristina

In addition to Genevieve's advice, please verify that the column you are inserting your formula into is formatted as Text/Number.

Kristina Todoric

Hi Kelly and Genevieve, thanks for both of your help!

The column is an auto/number column (created date) and Genevieve's revised formula worked.