COUNTIFS for Previous Month Data
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"))
Best Answer
-
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
Answers
-
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 Thank you! That worked perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!