If Formula not reading blank correctly

abbey123

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

Paul Newcome

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, .........................

abbey123

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

Paul Newcome

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.