Help with a COUNTIFS Formula by Month and by Year

Peppey

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

Lucas Rayala

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

Lucas Rayala

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

Peppey

Thanks Lucas that worked perfectly

Lucas Rayala

@Peppey glad it worked!