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

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!