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.