Trying to calculate the number of cells in a sheet during a specific year

mike.thorpe17421

I have dates in a series of columns on different sheets and want to calculate the number of cells where the date is within a range. i..e 1-Jan-22 to 31-Dec-22 or 1-Jan-23 to 31-Dec-23 or 1-Jan-24 to 31-Dec-24. The column in each sheet is exactly the same name (e.g. Launch date). Tried a number of options but just seem to get the right results.

Any help much appreciated.

Kind regards

Kelly Moore

Hello @mike.thorpe17421

The YEAR(date) function will allow you to gather the data you need within a COUNTIFS. Are you putting these into summary fields where you have a specific formula for each year- which means you are hard coding year values? Or, if you have a summary field that keeps a running total of the current year, you will refer to the YEAR of TODAY().

=COUNTIFS([Launch Date]:[Launch Date], YEAR(@cell) = 2022))

*note that numbers don't have quotes around them unless you want to force them to a text string (which in this case you do not)

Or, current year

=COUNTIFS([Launch Date]:[Launch Date], YEAR(@cell) = YEAR(TODAY())

Do either of these work for you?

Kelly

mike.thorpe17421

That's great Kelly, thank you. I couldn't seem to get the YEAR calculating though. This is the formula I have used when looking in another sheet.It should calculate how many launch dates for a specific country are in the current year. Country@ row points to the name of the country on my calculation sheet:

=COUNTIFS({Name of Sheet and Ref}, Country@row, {Name of Sheet and Ref}, YEAR(@cell) = 2022)). In your explanation above you have YEAR(@cell) = YEAR(TODAY())with Open Brackets after TODAY, is this correct?

Have I also got the last bit right as the first bit works ok: =COUNTIFS({Name of Sheet and Ref}, Country@row)

Sorry to be a bit dumb here but learning formulas all of the time.

Kelly Moore

Hey Mike

Glad you continue to ask questions! I see my countifs above is missing the ending parenthesis - I must have deleted it as I did my copy paste. If the last parenthesis isn't blue it means the parentheses count isn't correct.

For a cross sheet reference

=COUNTIFS({Other sheet Country column}, Country@row, {Other sheet Date column}, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))

Since this is a cross sheet reference, you must build each reference through the formula window. You cannot simply copy paste.

I added the IFERROR on the date as sometimes date functions can produce errors.

Does this work for you.

Kelly

mike.thorpe17421

Thanks Kelly, the TODAY function now works but cannot get the 2023, 2024 dates working?The formula I have is as follows for the YEAR date:

=COUNTIFS({Countries SSOT Ref Sheet for Dashboards L- Range 2}, Country@row, {Countries SSOT Ref Sheet for Dashboards J- Range 1}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

What would it be for 2023, I had used the above and changed to reflect and tried the following hut this doesn't work 😩

=COUNTIFS({Countries SSOT Ref Sheet for Dashboards L- Range 2}, Country12, {Countries SSOT Ref Sheet for Dashboards J- Range 1}, IFERROR(YEAR(@cell), 0) = 2023())

Kelly Moore

Hey Mike

The parentheses are associated with functions - we can fill them or not fill them depending on the data to be collected. When inserting a value, we just insert the value. If the value is a textstring it needs to be enclosed in quotes. If its a number then don't use quotes.

=COUNTIFS({Countries SSOT Ref Sheet for Dashboards L- Range 2}, Country12, {Countries SSOT Ref Sheet for Dashboards J- Range 1}, IFERROR(YEAR(@cell), 0) = 2023)

If you were just viewing the data and not recording/tracking the data, you could make the formula dynamic

=COUNTIFS({Countries SSOT Ref Sheet for Dashboards L- Range 2}, Country12, {Countries SSOT Ref Sheet for Dashboards J- Range 1}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())+1)

mike.thorpe17421

Hi Kelly

Many thnaks for your previous advise it was excellent and worked a treat. I am now trying to work on something similar but on a month to month basis.

Your formlula's for years work as follows:

IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + 0)

IFERROR(YEAR(@cell), 0) = YEAR(TODAY())+1)

Using the same thought process I replaced Year with Month as follows:

Current Month

IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + 0)

Next Month

IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + 1)

Any idea why this wouldn't work?

Also for the headings for the years they would be 2023, 2024, 2025, etc. To enable the headings to change year on year in line with the formulas have you any thoughts on how to do this? This would not be Column headings but a row heading.

Many thanks in anticipation.

Kind regards

Mike