If Formula not reading blank correctly
Hi,
Any chance you can help me figure out why my formula is not working and assist in fixing it.
The formula right below is the one I am trying to get to work and I am wondering if there is a way for the Purple box that says complete to say "not started" since it clearly has not been started.
=IF(COUNTIF(Status78:Status82, "At Risk") > 0, "At Risk", IF(COUNTIF(Status78:Status82, "Complete") + COUNTIF(Status78:Status82, "Not Applicable") = COUNT(Status78:Status82), "Complete", IF(COUNTIF(Status78:Status82, "Not Started") = COUNT(Status78:Status82), "Not Started", "In Process")))
Answers
-
our formula is referencing rows 78 to 82, but your screenshot shows rows 6 to 16. Try changing your ranges to CHILDREN().
=IF(COUNTIF(CHILDREN(), "At Risk")> 0, .........................
-
I made updates since posting this but either way it still doesn't work.
=IF(COUNTIF(Status23:Status24, "At Risk") > 0, "At Risk", IF(COUNTIF(Status23:Status24, "Complete") + COUNTIF(Status23:Status24, "Not Applicable") = COUNT(Status23:Status24), "Complete", IF(COUNTIF(Status23:Status24, "Not Started") = COUNT(Status23:Status24), "Not Started", "In Process")))
=IF(COUNTIF(Status27:Status28, "At Risk") > 0, "At Risk", IF(COUNTIF(Status27:Status28, "Complete") + COUNTIF(Status27:Status28, "Not Applicable") = COUNT(Status27:Status28), "Complete", IF(COUNTIF(Status27:Status28, "Not Started") = COUNT(Status27:Status28), "Not Started", "In Process")))
-
I still recommend changing the ranges to CHILDREN(), but I believe the issue may be that the Status cells in the child rows are blank which means this portion
COUNT(Status27:Status28)
is returning a count of zero. The count of Complete plus the count of Not Applicable is zero which equals the overall count of zero. Try changing it to a COUNTIFS to count cells that are either filled in or blank (to count all of them) or change the range in the COUNT function to a column that has data in it such as your primary column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!