Google-sheets – Date count downs in Google Sheets

google sheets

I was wondering if it is possible to create a formula in Google Sheets that counts down the days remaining in a month.

I would like the formula to go something like this:

=(Date of end of month - Today()) = Days remaining in the month (as a #, not date)

So for today the output for today would be 24.

The trick is to format the cell as a Number (don't leave it as automatic).

So, if you put the date at the end of the month (e.g., 1/31/2018) is cell A1, the formula

=A1-TODAY()

will result in "24" (if today is January 7, 2018). Assuming, of course, the format of the cell is correct.

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:

regexmatch(B3:D,"^[^MP]$") matches the cells that contain one letter which is neither M nor P.
if(...,1,0) converts boolean match result to 1 or 0.
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.
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)))
After the multiplication, countif(..., ">0") counts the positive entries.

You just seem to need a three-month running total and a multiplier. In C3 and copied down:

=sum(B1:B3)*$A$2

Ensure the C1:C2 cells do not contain anything that would be taken as a number.