Counting cells with previous year

hdierkers

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.

JamesB

@hdierkers

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

@hdierkers

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.

hdierkers

@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)?

hdierkers

@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).

JamesB

@hdierkers

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

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

JamesB

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], <>"")