3

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.

0

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.
  • Thank you SO much for your response and explanation! We are really close but though this is working in theory, it is not counting the rows that have any M or P anywhere. So in my above example, it would count "project 1" but not "project 2" or "3" and I would want it to count both 1 and 3. Basically I want it to pretend like the M and P are not even there. Is that possible? Or should I think of a way to make this work? – Laura D Aug 27 '15 at 16:53
  • I tested this command, and it returned 2 for your example. – user79865 Aug 27 '15 at 17:07
  • So strange, I put my example into a sheet to test and I am getting 3 now - it's counting every row. I'll keep playing with it but I really appreciate your help, this is closer than I've been in days! :) – Laura D Aug 27 '15 at 17:21
  • Ah, I see what it's doing. It is working for "MP" but not "M" or "P" in separate cells. – Laura D Aug 27 '15 at 17:23
  • It's meant to work for M or P individually. Try testing the regex part of the formula, =arrayformula(if(regexmatch(B3:D,"^[^MP]$"),1,0)) – user79865 Aug 27 '15 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.