how to use IF and COUNTIF in Excel 365 with multiple criteria with strings or text

Question

I have in several rows, lets say, from AX:40 to AX46, labels: "Not Available", "Available", and "Partially available".

The formula should be that if at least one row is "Partially available" or "Available", then return "Partially available", otherwise if ALL rows are labelled as "Not available", then return not available". If all rows are labelled as "Available", then return "Available". If all rows have again the same label but with "Partially available", then return "Partially available".

In other words:

If at least one has "Not available", then return "Partially available"

If at least one has "Partially available", then return "Partially available"

If at least one has "Available", then return "Partially available"

If all rows are "Not available", then return "Not available"

If all rows are "Partially available", then return "Partially available"

If all rows are "Available", then return "Available"

Answer 1

You could use this: =@SORT(VSTACK(AX40:AX46,IF(AND(ISNUMBER(XMATCH({"Available","Not Available"},AX40:AX46))),"Partially Available","")),,-1)

It checks for the range containing both the values Available and Not Available. If they're both present, it adds Partially Available to the values of range AX40:AX46 If not found it adds a blank. It than sorts the values of this array we created from largest to smallest. Partially Available - if present in the array - will always be the largest string ("P">"A" and "P">"N") The @ makes sure only that largest string value is returned; if you were to remove this, it'd return the sorted array as a spill.

If you run an older Excel than Office 365 (please make sure to mention, or tag in the question) you could use the following:

=IF(COUNTIF(AX40:AX46,"Available")*COUNTIF(AX40:AX46,"Not Available"),
    "Partially Available",
    IF(COUNTIF(AX40:AX46,"Partially Available"),
       "Partially Available",
       IF(COUNTIF(AX40:AX46,"Available"),
          "Available",
          "Not Available")))