Google Sheets – How to Count Rows Without Specific Text

google sheets

I need a formula that will tell me how many projects are going on at any given time in my Google spreadsheet. My table header looks like this:

Project | Jan 1-5 | Jan 6-10 | Jan 11-15...

And each row will either be empty, or have text in the different cells (to show what was happening on a given date). For instance:

Project 1 | U | U | L  
Project 2 | P | M | M  
Project 3 | M | O | K

I found this formula which works very well, however, it counts each row with a value. I need to add a criteria that says that says not to count "M" or "P" as a value. 

=ArrayFormula(
  SUM(SIGN(MMULT(LEN(Sheet1!B3:E),TRANSPOSE(SIGN(COLUMN(Sheet1!B3:E))))))
)

For the example above, this would give me the answer of 3, but I am looking for it to say 2.

Best Answer

Here is an approach similar to yours; the filtering is done by regexmatch in the middle. 

=countif(mmult(
   arrayformula(if(regexmatch(B3:D,"^[^MP]$"),1,0)),
   transpose(arrayformula(column(B3:D)))
),">0")

Explanation:

  1. regexmatch(B3:D,"^[^MP]$") matches the cells that contain one letter which is neither M nor P.
  2. if(...,1,0) converts boolean match result to 1 or 0.
  3. arrayformula applies the above to the whole array, obtaining a matrix of 1s and 0s. It remains to count the number of rows that contain at least one 1.
  4. As in your formula, this is done by multiplying the matrix by a vector with positive entries and counting the number of positive results. The vector I use is transpose(arrayformula(column(B3:D)))
  5. After the multiplication, countif(..., ">0") counts the positive entries.
Related Topic