# Formula to Count Data Broken Down by Month

edited 12/09/19

I am wanting to use a formula to count data in various columns based on specific time period. Example for the month of October, I would like to count various column data and when it rolls into November I would like the formula to continue to work without changing it.

I am able to use my formula (see below) to capture exactly what I need between 9/30/19 and 11/1/19 but it seems that my current approach to this will require to me to go into the formulas for each calculation at the start of the next month and modify. My hopes are that there is an easier way to approach this.

Formula (gives me what I need):

=COUNTIFS([Date of RMA Request]:[Date of RMA Request], >DATE(2019, 9, 30), [Date of RMA Request]:[Date of RMA Request], <DATE(2019, 11, 1), [RMA Type Code]:[RMA Type Code], "28-Site Survey Error")

Tags:

• ✭✭✭✭✭✭

Try something like this...

=COUNTIFS([Date of RMA Request]:[Date of RMA Request], AND(IFERROR(MONTH(@cell), 0) = 10, IFERROR(YEAR(@cell), 0) = 2019), [RMA Type Code]:[RMA Type Code], "28-Site Survey Error")

• Thank you for the insight- will this formula allow for the metric to maintain current month count?

Another words, when we roll into November will it continue to count November?

• ✭✭✭✭✭✭

No it will not. The 10 is the month number you are looking for (same with 2019 being the year number), so replacing those values with functions to automate those numbers is what you are going to need to do.

MONTH(TODAY())

YEAR(TODAY())

Put those in place of the month and year numbers, and the formula will automatically look for the current month.

• Thank you Paul. I am going to try it today and see what I get.

• ✭✭✭✭✭✭
• Any idea what I am missing here? This is what I have in and I am getting #UNPARSEABLE error message

=COUNTIFS([Date of RMA Request]:[Date of RMA Request], AND(IFERROR MONTH(TODAY()), IFERROR(YEAR(TODAY()), [RMA Type Code]:[RMA Type Code], "28-Site Survey Error")

• ✭✭✭✭✭✭

You aren't finishing out the logical statements or IFERROR functions within your AND statement which in turn has left your AND statement open.

.

Take my previously posted formula and replace the number 10 and the number 2019 with

MONTH(TODAY())

and

YEAR(TODAY())

respectively.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!