Help with a COUNTIFS Formula by Month and by Year
Hi everyone! I am hoping you can assist me. I need to count the number of times a specific action is completed by month and year. The column with the actions is titled "FFP Line-Item Number", which appears as Range 1 in the formula. The one thing I see as a possible problem is the person who designed the tracker has multiple actions in the same column ("FFP Line-Item Number".
I am trying to put a formula that will tell me how many "1.2.1.1: Basic FES Evaluations" we completed for January 2023 (which is identified in the formula as Range 2). Below is the formula that shows 0, but it should show we completed two actions for January 2023.
=COUNTIFS({HRMSS 02 Classification Tracker FY23 Range 1}, "1.2.1.1: Basic FES Evaluation", {HRMSS 02 Classification Tracker FY23 Range 2}, IFERROR(MONTH(@cell), 0) = 1, {HRMSS 02 Classification Tracker FY23 Range 2}, IFERROR(YEAR(@cell), 0) = 2023)
Any help would be appreciated!
Brian
Best Answer
-
Hi @Peppey, your range 2 is a date column, correct? You can just provide a date range in your Range 2 criteria using an "AND" to indicate two criteria must be met and use the DATE function to enter your date ranges, as below.
=COUNTIFS({HRMSS 02 Classification Tracker FY23 Range 1}, "1.2.1.1: Basic FES Evaluation", {HRMSS 02 Classification Tracker FY23 Range 2}, AND(@cell>=DATE(2023, 01, 01), @cell<=DATE(2023, 01, 31)))
Answers
-
Hi @Peppey, your range 2 is a date column, correct? You can just provide a date range in your Range 2 criteria using an "AND" to indicate two criteria must be met and use the DATE function to enter your date ranges, as below.
=COUNTIFS({HRMSS 02 Classification Tracker FY23 Range 1}, "1.2.1.1: Basic FES Evaluation", {HRMSS 02 Classification Tracker FY23 Range 2}, AND(@cell>=DATE(2023, 01, 01), @cell<=DATE(2023, 01, 31)))
-
Thanks Lucas that worked perfectly
-
@Peppey glad it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!