# Counting cells with previous year

I have a range of cells in a different sheet, and I am trying to create a cross-reference formula to count the cells that are in a past year. In the referenced sheet, I have years 2011-2023 listed, and I want to count the cells that are not marked as 2023 that fall under a certain criteria.

• ✭✭✭✭✭✭

A Countif Formula is your starting point, basic formula below. If you have additional criteria then a countifs will be needed. If your reference data is a date field you will need:

=Countif(Year({Reference Sheet Data}),<2023)

if it is just a Text/Number Field then:

=Countif({Reference Sheet Data},<2023)

If you can provide more context on what your sheet data looks like and your criteria, assistance on creating a more complete formula can be provided.

• ✭✭✭✭✭✭

A Countif Formula is your starting point, basic formula below. If you have additional criteria then a countifs will be needed. If your reference data is a date field you will need:

=Countif(Year({Reference Sheet Data}),<2023)

if it is just a Text/Number Field then:

=Countif({Reference Sheet Data},<2023)

If you can provide more context on what your sheet data looks like and your criteria, assistance on creating a more complete formula can be provided.

• @JamesB - This worked!

So, for additional context - I am looking across completed projects for years past and then this year. I am using

=COUNTIFS(({LEGACY Submissions Project List Range 1}), <2023, {Legacy Project List Range 2}, ="Completed")

Then, for additional criteria (ie, which country was a project completed in), I am continuing to string the range and criteria. Is there a way to add on a specification to only show this information for non-blank cells (ie, Projects within the LEGACY Submissions Project sheet, that were completed in the US in 2023, omitting cells that do not have a project type)?

• @JamesB I thought of an issue with the formula. I would like to always calculate from the previous years, so the formula above will not work anymore in 2024 (or I'll have to update the formula to be <2024) - is there a way to change the properties of the source sheet or the formula to automatically be previous years? The column type in the source sheet is a text/number (I am only capturing year, not a specific date).

• ✭✭✭✭✭✭
edited 08/15/23

Yes. You need to replace <2024 with <Year(Today())

=COUNTIFS(({LEGACY Submissions Project List Range 1}), <YEAR(Today()), {Legacy Project List Range 2}, ="Completed")

• ✭✭✭✭✭✭

For your non-blank cells you should be able to use <>"" as the criterion for looking at the Project type cell:

example:

=COUNTIF([Project Type]:[Project Type], <>"")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!