One option:
=COUNTIF(B:B; "Mammal") + COUNTIF(B:B; "Bird")
According to the documentation:
Notes
COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA.
COUNTIFS: This function is only available in the new Google Sheets.
Example:
=DCOUNTA(B:B; 2; {"Type"; "Mammal"; "Bird"})
regex - How do I combine COUNTIF with OR - Stack Overflow
Using OR in a countif or countifs
COUNTIFS multiple criteria in Google sheets - Stack Overflow
google sheets - Countifs with OR criterias and ranges - Stack Overflow
One option:
=COUNTIF(B:B; "Mammal") + COUNTIF(B:B; "Bird")
According to the documentation:
Notes
COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA.
COUNTIFS: This function is only available in the new Google Sheets.
Example:
=DCOUNTA(B:B; 2; {"Type"; "Mammal"; "Bird"})
You can also use ArrayFormula around a SUM(COUNTIFS()) construct:
=ArrayFormula(SUM(COUNTIF(B:B,{"Mammal", "Bird"}))
Source: Google Docs Product Forum
Assume the following values in A1:A4 -
FOO BAR FOO FOOFYBAR
I'd like to count how many times the range contains FOO or BAR. The correct answer is 3.
One would think I would use this formula:
=countif(A1:A4,{"FOO","BAR"})
Alas, that yields only 2.
Conversely, this should be functionally the same formula:
=countif(A1:A4,{"BAR","FOO"})
But that yields 1.
It seems that it stops after counting the instances of the first value. How can I get it to count instances of all the values within the braces?
This sounds like a case for a pivot table. Select the data table including the headings(!), use the "Insert" menu and insert a pivot table - you can put it onto the same sheet or on a new one, it doesn't matter.
Then add an entry in "Rows", pick the "Name" field. Next add one "Values" entry, select the "Quantity" field. The sum of quantity per name is automatically added.
To filter values, you have to add extra filters.
Looks something like this:
Use COUNTIF and SUMIF
To get the number of times the string was present, use COUNTIF. On the other hand, you can use SUMIF to get the sum of the quantity based on the string. You may use the following:
=LET(a,TRANSPOSE(SPLIT(JOIN(",",UNIQUE(A2:A)),",")),b,byrow(a,lambda(y,COUNTIF(A2:A,y))),c,byrow(a,lambda(x,SUMIF(A2:A,x,C2:C))),HSTACK(a,b,c))
Output
Note: The headers are not part of the formula so you can just manually add them manually.
References:
- COUNTIF()
- SUMIF()
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)''"))
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.
countifs
One way is to use countifs(..., D:D, "New") + countifs(..., D:D, "Active") -- addition works as long as the options in OR are mutually exclusive.
filter
More generally, the combination counta(filter(...)), which is more powerful than countifs, can express OR logic by addition:
=counta(filter(A:A, A:A = "name", B:B = "zone", (D:D = "New") + (D:D = "Active"))
query
Yet more generally, query can return such results for all names and zones at once:
=query(A:D, "select A, B, count(D) where D = 'New' or D = 'Active' group by A, B', 1)
Further reading: filter, query.
COUNTIFwithORcould be solved like this:=ARRAYFORMULA(SUM(N(REGEXMATCH(A1:A, "new|active"))))
finding for one value:
=ARRAYFORMULA(COUNTA(IFERROR(FILTER(A1:A, A1:A="Zone A", REGEXMATCH(B1:B, "new|active")))))
finding for all values:
=QUERY(A:B, "select A,count(B) where B matches 'active|new' group by A label count(B)''", 0)
In Excel
=SUM(COUNTIF(range,{"Broccoli","Pears"}))
(to be entered as an array formula if you don't have dynamic arrays)
In Sheets
=arrayformula(sum(countif(range,{"Broccoli","Pears"})))
Check out this doc: https://support.google.com/docs/answer/3093480?hl=en.
Basically, you do =COUNTIF(A1:A10,"Broccoli").
You can also do =COUNTIF(A1:A10,A1), where the 2nd param, the criteria, is equal to the text content in a specific cell, A1 in the example. This is a quick example I tested.