Using CountIf with multiple columns and dates
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
-
Change @CELL to @cell
Answers
-
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.
-
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")
-
@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. 🤣
-
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)
-
Change @CELL to @cell
-
Thank you KDM. That worked
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!