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!

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

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

Jeniese Chaffin

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

Kelly Moore

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

Jeniese Chaffin

That worked PERFECTLY! THANK YOU!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.