CODEWITHSUNDEEP
arraysregexgoogle-sheetsfiltersum
MegaMikeJr
MegaMikeJr

Reputation: 145

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.

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

Upvotes: 1

Views: 51

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(SUMPRODUCT(REGEXMATCH(FILTER(A:A, A:A<>""), B22)=FALSE))

enter image description here

or:

=SUM(INDEX(N(REGEXMATCH(FILTER(A:A, A:A<>""), B22)=FALSE)))

Upvotes: 2

Related Questions