google sheets
For Google Sheets, there's plenty on how to format based on length, and there's plenty on how to count spaces, but I'm struggling to combine the formula.
I understand the usage of ranges for formatting on length:
=len(A:A)>30
And for counting spaces:
=len(A1)-len(SUBSTITUTE(A1," ","")
But how do I apply ranges to the above so that it can be used in conditional formatting?
My goal is to format a cell red if there are more than N spaces in it.
The functions you need to use are SUMIF and COUNTIF.
For example, to count all the bulbs with DateFilterCleaned within six months, and assuming that DateFilterCleaned is within six months, you need to:
Make a helper-column which calculates the number of days eg E3 = now()-D3
And then a formula like this
=COUNTIF(E:E, ">183")
To make it general purpose for each school year, just put the start-date and end-date into separate cells (eg F1 and F2) and refer to them using absolute addressing (eg $f$1 and $f2)
(This isn't the full solution, but is hopefully enough hints to get you started. Note that there is absolutely no need to use scrips for any of this.)
You can use a range as a parameter for the ISFORMULA() function.
e.g. If you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1:J10) and apply it to the range A1:J10.
Note that this works with normal ranges (e.g. B2:F30), but not with infinite ranges (e.g. B:F).
Best Answer
Try using the formula:
and the Range of the Conditional Format as A:A