arraysregexgoogle-sheetsfiltersum
How do you count cells using regex that do not match the expression?
I want to count the number of cells that do not contain the following words.
- denv
- univ
- du
The above list of words change frequently and in Cell B22 it automatically creates some regex for another formula where I sum another column next to it.
Cell B22 = .*denv.*|.*univ.*|.*du.*
Can I use the same Cell B22 reference for counting everything that DOES NOT contain those words?
| Name | Metric |
|---|---|
| denver | 5 |
| ohio | 5 |
| dual | 9 |
| dual | 1 |
| maryland | 4 |
| universe | 6 |
| maryland | 1 |
| dual | 2 |
| denver | 7 |
Solution
try:
=INDEX(SUMPRODUCT(REGEXMATCH(FILTER(A:A, A:A<>""), B22)=FALSE))
or:
=SUM(INDEX(N(REGEXMATCH(FILTER(A:A, A:A<>""), B22)=FALSE)))
- nodeJS util.format passing an array
- syntax for string array migration
- Error: non-static initialization of a flexible array member in Some Cases but Not Others
- array manipulations and extractions of data by names
- Expected expression before <= token
- Why does a for-in loop over an array not iterate over its elements?
- assign a string to a structure variable in c
- How to stable sort an array in swift?
- Excel ActiveSheet.Shapes.Range(MyArray).Select
- Perl calculate average from randomly populated array
- Bash. The quickest and efficient array search
- How to calculate the median of an array?
- Duplicate an array an arbitrary number of times (javascript)
- Find all objects with matching Ids javascript
- Remove first and last element from an array
- Pointer interconvertibility vs having the same address
- Zip arrays in JavaScript?
- Python split() function :: Need to split "int_32\n' " so that I get int_32 alone
- How to fetch the first matching item from an array
- How to save the result of a variable in a loop
- PHP arrays, how to access keys and values
- Randomly select 2 elements from an array where each element is selected 4 times and the pairs are not duplicated
- Adding a break to a bubble sort in case the array is already sorted
- Convert 2-D array to 1-D array & paste 1-D array to another sheet
- Comparing two byte arrays in .NET
- How to destructure for loop parameters into array of fixed size?
- Are arrays passed by value or passed by reference in Java?
- How to use Array.from with a XPathResult?
- Printing out a 2D array in matrix format
- Are arrays treated as static data structures by gcc compiler or as dynamic?