column A has landowner. Column B has substation. Column C has status of column A.
I want to see if all landowners in a substation do not have a status (status = "").
I created in column D a unique list of substations (=unique(B2:B)). I used this formula to check to see if an entire substation has "" for status.
=ARRAYFORMULA(IF(D2:D="", "",
IF(COUNTIF(FILTER(C:C, B:B=D2:D), "") =
COUNTIF(B:B, D2:D), "All Empty", "Not All Empty")))
but its not working because there is a substation called Calmar that has no statuses:
but in my calc it says Not All Empty when it should read Empty
>Solution :
Try with this slight modification of your formula from arrayformula to map\lambda
=map(D2:D,lambda(Σ,IF(Σ="", "",
IF(COUNTIF(FILTER(C:C, B:B=Σ), "") =
COUNTIF(B:B, Σ), "All Empty", "Not All Empty"))))