Countifs with OR criterias and ranges

Question

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

Your question can be greatly improved if you add input table and expected output table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. — TheMaster, Sep 22 '22 at 10:19

player0 · Answer 1 · 2022-09-22T11:10:08.573

1

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

edited Sep 22 '22 at 11:10

answered Sep 22 '22 at 10:30

player0

124,011
12
67
124

I am sorry. I reviewed the explanation and I did not express my selft correctly (ENG is not my mother language). I found how to determine if they are COLOR or BLACK (color is when they have at least something different from empty or black in one of the 5 columns) using a combination of ifs with ORs and ANDs. The problem is I cannot embed that in a countif because we are comparing the result of a formula that check row by row with something I want to check globaly in a complete Column. And I want to not need using that extra row with "COLOR" and "BLACK", just count them in another sheet. – Daniel del Solar Sep 22 '22 at 10:48
@DanieldelSolar can you share a copy of your sheet with example of final desired result? – player0 Sep 22 '22 at 10:53
@DanieldelSolar answer updated – player0 Sep 22 '22 at 11:13

Tom Sharpe · Answer 2 · 2022-09-23T11:15:22.377

1

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.

Note

If your database gets large and you get problems with Byrow/Lambda you can revert back to the more conventional Mmult:

=ArrayFormula(countif(mmult(n((L2:P<>"Black")*(L2:P<>"")),sequence(5,1,1,0)),">0"))

and

=ArrayFormula(countif(mmult(n(L2:P<>""),sequence(5,1,1,0)),">0"))-C2

assuming previous formula is in C2.

edited Sep 23 '22 at 11:15

answered Sep 22 '22 at 11:54

Tom Sharpe

30,727
5
24
37

Hi Tom, Do you know any references to the actual memory limit/calculation limit of lambda functions ? – TheMaster Sep 22 '22 at 12:04
Interesting question! No, I don't at time of writing. – Tom Sharpe Sep 22 '22 at 12:06
@TheMaster I heard its 10k - I did not verify it tho – player0 Sep 22 '22 at 12:35
@player0 Where did you hear it? In practice, It's way less than normal functions..., like 50 times less I keep running into limits. – TheMaster Sep 22 '22 at 12:43
1

I tried it with a 1d array and it failed at around 100K. This works for example though =sum(byrow(sequence(99990,100),LAMBDA(r,sum(r)))) but admittedly it's a very simple lambda. – Tom Sharpe Sep 22 '22 at 13:07
Added a question https://stackoverflow.com/questions/73815258/what-factors-determine-the-memory-used-in-lambda-functions cc: @player0 – TheMaster Sep 22 '22 at 13:12
@TheMaster https://chat.stackoverflow.com/rooms/248164/room-for-kishkin-and-player0 – player0 Sep 22 '22 at 13:32
The formula in my answer worked at 32K but failed at 64K, I haven't had time to investigate further. – Tom Sharpe Sep 22 '22 at 17:31

Osm · Answer 3 · 2022-09-26T04:35:37.057

0

Use this formula

=ARRAYFORMULA(LAMBDA(u, {UNIQUE(u),COUNTIF(u, "="&UNIQUE(u))})
             (LAMBDA(t, IFS(t=FALSE,"Color",t=TRUE,"Black"))
             (REGEXMATCH(trim(LAMBDA(f, FILTER(f,f<>""))(FLATTEN(L2:P))), "(?i)black"))))

Demo

edited Sep 26 '22 at 04:35

answered Sep 26 '22 at 04:21

Osm

2,699
2
4
26

Countifs with OR criterias and ranges

3 Answers3

update:

Demo