Using CountIf with multiple columns and dates

Matt Miller

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

Kelly Moore

@Matt Miller

Change @CELL to @cell

Mike Wilday

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

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

@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

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

@Matt Miller

Change @CELL to @cell

Matt Miller

Thank you KDM. That worked

Chris Medina

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

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}, ...