COUNTIFS for prior period

Jess D

Hi,

I am looking to build a dashboard that displays prior month/quarter/year data. I have found ways to do this from a rolling perspective (Year (Today())-1) or specifying a year, such as (Year='2023')@Row, but none that appear to be scalable. I don't want to have to recreate any summary fields (with formulas)/reports/dash each new year and want the count within the period, not rolling relative to today. Is there a way to make the formula flexible do handle this?

Paul Newcome

Are you able to provide some examples for context?

Jess D

We are capturing requests from one team to another in Smartsheets. This has been going on less than a year, but there are plans to expand it and increase the volume as well as run for many years.

So right now I can create formulas to show X volume of requests occurred in December and Y volume in January. But I want the formula to be able to automatically adjust so that I don't have to manually change the dates (after X date, and before Y date) inside the formula to ensure it is capturing just the requests in the past month. This would also apply for quarters and years.

Also, the rolling doesn't work because, say today, I want to know how many were in January, not between Jan 13 and Feb 12 when using a formula like (TODAY()-30.

Does that help better explain what I am looking for?

Paul Newcome

There are a few different ways to accomplish this depending on your exact needs. Are you just wanting "previous month" or are you wanting to go back a set number of months?

Jess D

I'm looking to use one formula that can continually provide the prior month data without need to modify the formula. So in Feb I want to know how many requests there were in January, that same summary field should reflect the Feb data when we enter March, etc.

I use month as an example, but I am tracking month, quarter, and year trends in data.

The summary field is flowing to a summary report and from there to a chart on a dashboard. I need the dashboard to be accurate and current with minimal maintenance.

Paul Newcome

Previous month:

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)))

Previous Year:

=COUNTIFS({Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)

Previous Quarter:

Little bit trickier and will have to get back to you on that one. Are your quarters lined up with the year so that Q1 is Jan - Mar, Q2 is Apr - Jun, so on and so forth?

Jess D

Thanks Paul. Yes, we do use calendar quarters.

I was worried about using the TODAY() variable in case it blurred the lines. That could be my lack of understanding. I was worried that the MONTH(TODAY()-1 ) would be a rolling result, so within the past month from today, so today being 2/13/24, it would not capture January 1 - January 12 data. Is this my misunderstanding that?

Paul Newcome

The parenthesis are key here.

MONTH(TODAY() - 1)

will give you the month number from yesterday.

What I have

MONTH(TODAY()) - 1

will subtract one from the current month.