Calculate Entries by Month and Year

HarryS

Hi everyone,

I am currently capturing the month and year an item is closed. Colum A shows the Month, column B shows the Year ([Jan] [2024]). Can someone tell me the formula that I can use to count the items that are closed in a particular month?

To calculate the entries closed in Jan I've tried using:

=COUNTIFS([Request Reporting Month]:[Request Reporting Year], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

But the total comes out as zero. Which is wrong.

Thanks for the assist!

Paul Newcome

Change your range to evaluate a single date type column.

HarryS

https://community.smartsheet.com/discussion/comment/422777#Comment_422777

I've tried using only Request Reporting Month, and then only Request Reporting Year and they both show zero.

Paul Newcome

Instead of evaluating your helper columns, evaluate the date column directly. Or are you manually selecting month and year in two separate columns? Are you able to provide a screenshot for context?

HarryS

https://community.smartsheet.com/discussion/comment/422837#Comment_422837

Sure thing. This is what I'm working with.

Created date is the date of submission. Reporting month and year is the month/year in which Smartsheet records the timeframe the request was created. I'd like to use these two columns for reporting purposes via sheet filter or summary.

HarryS

If I use this forumula the summary correctly counts the entries per month, but I have to manually change the year to get the current years data:

In this example it correctly counts the entries for MARCH 2023:

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2023))

To get the results from 2024 I have to edit the formula to 2024. I am trying to figure out way to report on the numbers by month/year without having to manually edit the formula.