Formula to Count Data Broken Down by Month

James P Garcia

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

 

 

Paul Newcome

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

James P Garcia

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?

Paul Newcome

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.

James P Garcia

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

Paul Newcome

Happy to help! 

James P Garcia

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

Paul Newcome

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.