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.
Answers
-
>{AIR-360 Due Date}
- this is a criteria and I think it needs to be a single item....
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!