Help with a countifs formula
Hello!
I am using a formula to count if there is a checkmark in a column during a certain month and year. The formula is as follows and works perfectly:
=COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, =1, {MCFS Safety - Service Recovery Date of Incident 1}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))
My need for help if with the following:
I tried to use the same formula to count if the checkbox was unchecked and it gives me an error message. The following is what I did:
=COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, 0 , {MCFS Safety - Service Recovery Date of Incident 1}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))
Why do I get an error for the second formula when all I did was change the "1" to a "0"?
thank you!
Answers
-
Hey @Jeniese Chaffin
What error message do you receive? Is there an extra space between the zero and the next comma?
Also, if you choose to convert later to convert the formula to a column formula, you will need to remove $'s from the formula.
Let me know on your error message
-
Thank you! I removed the space after the "0" but it still gave me a #Invalid Data Type error message.
This is the formula I used:
=COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, =0, {MCFS Safety - Service Recovery Date of Incident 1}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))
-
Hey Jeniese
Date functions often produce errors if there are any blanks or non dates in the range. The IFERROR function will execute a function or formula if there isn't an error, but if there is, it will insert whatever is designated - in this case a zero.
Try this
=COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, =0, {MCFS Safety - Service Recovery Date of Incident 1}, AND(IFERROR(MONTH(@cell),0) = [Month #]@row, IFERROR(YEAR(@cell),0) = [Year #]@row))
cheers
-
That worked PERFECTLY! THANK YOU!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!