COUNTIFS for Previous Month Data

Christian Eveleigh

I am trying to create a metric for a Dashboard and need a count of items that were submitted the month before current. I am using as reference "{HDP Late Add Request Request Date}" column. I am currently counting the metrics for the current month and the formula is working. I am not sure where to add the criteria to the formula to develop the metric for the previous month.

This is the formula that i have been using for current month count

=COUNTIFS({HDP Late Add Request Request Date}, IF(ISDATE(@cell), MONTH(@cell)) = MONTH((TODAY())), {HDP Late Add Request Reason for Late Add}, OR(@cell = "Materials Availability"))

Kelly Moore

Hey @Christian Eveleigh

Last month is one less than this month, so we need to add that to your criteria. The watchout is to make sure you are subtracting 1 from the MONTH function and not from the TODAY function.

As an alternative to your formula above with an embedded IF, you could use IFERROR to mitigate empty date values. This will help simplify formulas when you have multiple date criteria to contend with. Also, with only one criteria in your Reason for Late Add you do not need an OR.

=COUNTIFS({HDP Late Add Request Request Date}, IFERROR(MONTH(@cell),0) = MONTH(TODAY())-1, {HDP Late Add Request Reason for Late Add}, @cell = "Materials Availability")

Will this work for you?
Kelly

Kelly Moore

Hey @Christian Eveleigh

Last month is one less than this month, so we need to add that to your criteria. The watchout is to make sure you are subtracting 1 from the MONTH function and not from the TODAY function.

As an alternative to your formula above with an embedded IF, you could use IFERROR to mitigate empty date values. This will help simplify formulas when you have multiple date criteria to contend with. Also, with only one criteria in your Reason for Late Add you do not need an OR.

=COUNTIFS({HDP Late Add Request Request Date}, IFERROR(MONTH(@cell),0) = MONTH(TODAY())-1, {HDP Late Add Request Reason for Late Add}, @cell = "Materials Availability")

Will this work for you?
Kelly

Christian Eveleigh

@Kelly Moore Thank you! That worked perfectly!