Using CountIf with multiple columns and dates

Options

I am attempting to Count certain instances across 2 columns. Example, I want to count all Near Misses that occurred in February of 2021. I have figured out how to count everything that occurs in February. =COUNTIFS([Date Reported to Manager]:[Date Reported to Manager], IFERROR(MONTH(@cell), 0) = 2, [Date Reported to Manager]:[Date Reported to Manager], IFERROR(YEAR(@cell), 0) = 2021)

I also can count the Near Misses.

=COUNTIFS([Injury-Illness-Near Miss]:[Injury-Illness-Near Miss], ="Near Miss")

But when I combine the formulas they do not work.

=COUNTIFS([Date Reported to Manager]:[Date Reported to Manager], IFERROR(MONTH(@cell), 0) = 2, [Date Reported to Manager]:[Date Reported to Manager], IFERROR(YEAR(@cell), 0) = 2021), IFError( ([Injury-Illness-Near Miss] : [Injury-Illness-Near Miss]),0)= "Near Miss")

Best Answer

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Try combining the two formulas with simple math.

    =COUNTIFS([Date Reported to Manager]:[Date Reported to Manager], IFERROR(MONTH(@cell), 0) = 2, [Date Reported to Manager]:[Date Reported to Manager], IFERROR(YEAR(@cell), 0) = 2021) + COUNTIFS([Injury-Illness-Near Miss]:[Injury-Illness-Near Miss], ="Near Miss")

    Did that work? Sometimes I overthink things! 😁 Simple math can be our best friend at times.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Matt Miller

    It looks like your last IFERROR is built incorrectly.

    =COUNTIFS([Date Reported to Manager]:[Date Reported to Manager], IFERROR(MONTH(@cell), 0) = 2, [Date Reported to Manager]:[Date Reported to Manager], IFERROR(YEAR(@cell), 0) = 2021), IFERROR([Injury-Illness-Near Miss]:[Injury-Illness-Near Miss],0)= "Near Miss")

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    @KDM you're right. I retract my answer! You don't want double counts. You want more finely filtered results/ 😫 I need to slow down and read. 🤣

  • Matt Miller
    Options

    Still comes back Unparseable.

    I tried this and it worked for the month.

    =COUNTIFS([Injury-Illness-Near Miss]:[Injury-Illness-Near Miss], "Near Miss", [Date of Injury or Near Miss]:[Date of Injury or Near Miss], MONTH(@cell) = 1)

    But when I add the Year(@cell)=2021 it comes back Unparseable

    =COUNTIFS([Injury-Illness-Near Miss]:[Injury-Illness-Near Miss], "Near Miss", [Date of Injury or Near Miss]:[Date of Injury or Near Miss], MONTH(@cell) = 1, [Date of Injury or Near Miss]:[Date of Injury or Near Miss], YEAR(@CELL) = 2021)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    @Matt Miller

    Change @CELL to @cell

  • Matt Miller
    Options

    Thank you KDM. That worked

  • Chris Medina
    Options

    I'm having a similar problem, but in referencing another sheet:=COUNTIFS({[CompMed Staff Range 2]}:{[CompMed Staff Range 2]}, > [Start Date]@row) is returning an unparsable error. I'm creating an Attrition list with the start date column filled in (1/1/21) and referencing another list that has their Date of Hire (this is the CompMed Staff Range 2 entry). Thoughts?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    One thing i noticed is that you're cross referenced range looks weird. You shouldn't need the colon : in the range... try replacing your range with just {compMed Staff Range 2}

    =Countifs({compMed Staff Range 2}, ...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!