Can Anyone Help on COUNTIFS Formula with Dates?

I have a calculation sheet with the formula =COUNTIFS({Sent to AIR-360}, >[FY Start Date]@row, {SR Type}, ="NTSB", {Sent to AIR-360}, >{AIR-360 Due Date}). The formula is to count dates greater than FY START DATE (Date Column), that equal NTSB (Dropdown text column) and Sent to AIR-360 dates that are greater than the AIR-360 Due Date. The purpose is to count the number of late responses for a given fiscal year. The above formula returns 0 regardless of criteria order.

I have isolated that the issues is with "{Sent to AIR-360}, >{AIR-360 Due Date} part of the formula. These are both date columns in another sheet. When I remove them, the formula returns a result. When I remove all other criteria from the formula, it returns 0. Since the AIR-360 Due Date column has some null values, I add dates, but still get 0 as a result.

Anyone have on what I am missing here? Thank you for your help.

Find more posts tagged with

Formulas

Accepted answers

All comments

heyjay

>{AIR-360 Due Date} - this is a criteria and I think it needs to be a single item.

Neela_15

Thank you heyjay. I am not sure I understand what you are getting at. Part of the criteria before it will count is that the Sent to AIR-360 cell is greater than AIR-360 Due Date to determine if the response was late.

Neela_15

Problem solved. Thinking about heyjay's answer, I created an additional column called Late Response where the comparison between {Sent to AIR-360}, >{AIR-360 Due Date} was made in the source sheet, enabling me to make a s single reference within the COUNTIF formula, which now looks like =IFERROR(COUNTIFS({Sent to AIR-360}, >[FY Start Date]@row, {SR Type}, ="NTSB", {Late Response}, ="Yes"), "0"). 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.