Countifs with OR criterias and ranges-CodePudding

I have 5 Columns with color options. I need to COUNT how many of them are different of Black in all options. I mean, the momment they have something different from black in one of the 5 columns, they are consider color. If not, they are considered as black. I.e, if "Red" appears in one of the 5 colums, is COLOR. I have tried to add OR inside Countifs, and using querys but it does not work. The only way I found is using a new columns with if conditions but i need to embed that in the count or query formula (I cannot add any new column in the main datasheet).[ I cannot embed pics yet so there is an screenshot.]

=if(OR(if(AND(L2<>"Black";L2<>"");"COLOR";"")="COLOR";
   if(AND(M2<>"Black";M2<>"");"COLOR";"")="COLOR";
   if(AND(N2<>"Black";N2<>"");"COLOR";"")="COLOR";
   if(AND(O2<>"Black";O2<>"");"COLOR";"")="COLOR";
   if(AND(P2<>"Black";P2<>"");"COLOR";"")="COLOR");"COLOR";"BLACK")

I have used that formula as an interface and I can count how many of them are COLOR or BLACK. It works, but I need the result without adding any additional column.

I know that any satement after "comma" in countifs is an "AND" so any range and condition is limiting needing to acomplish all the conditions. I need they work as "ORs" but I did not found a way. I would need something like:

=countifs(AND(L:L,<>"Black"L:L<>"") *OR*  AND(M:M,<>"Black"M:M<>"") *OR* AND(N:N,<>"Black"N:N<>"") ...

CodePudding user response：

try:

=INDEX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(L2:P),,9^9)))="",,
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(L2:P),,9^9)), "(?i)black"), "BLACK", "COLOR")))

or:

=INDEX(IF(LEN(L2:L&M2:M&N2:N&O2:O&P2:P), UPPER(MAP(L2:L, M2:M, N2:N, O2:O, P2:P, 
 LAMBDA(L,M,N,O,P, IFS(L="BLACK",L,M="BLACK",M,N="BLACK",N,O="BLACK",O,P="BLACK",P,TRUE,"COLOR")))), ))

update:

=INDEX(QUERY(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(L2:P),,9^9)))="",,
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(L2:P),,9^9)), "(?i)\bblack\b"), "BLACK", "COLOR")), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''"))

CodePudding user response：

You could do it like this in I2 say

=countif(byrow(L2:P,lambda(r,countifs(r,"<>Black",r,"<>"))),">"&0)

and in J2

=countif(byrow(L2:P,lambda(r,counta(r))),">"&0)-I2

Add a sheet reference if you need the formula to be in a different sheet e.g.

=countif(byrow(Sheet6!L2:P,lambda(r,countifs(r,"<>Black",r,"<>"))),">0")

=countif(byrow(Sheet6!L2:P,lambda(r,counta(r))),">0")-A2

if in A2 and B2 of a separate sheet.