Explanation
The COUNTIF function counts cells that meet supplied criteria, and returns a count of occurrences found. If no cells meet criteria, COUNTIF returns zero.
The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in D5, the formula will count the value as a substring. In other words, it will count the value if it appears anywhere inside any cell in the range.
Any positive result means the value was found. By comparing the result with the greater than operator (>) and zero, we force a final result of TRUE or FALSE.
With IF
You can nest this formula inside the IF function as the logical test. For example, to return a final result of “Yes” or “No”, you can use IF like this:
=IF(COUNTIF(range,"*"&value&"*"),"Yes","No")
Explanation
The first thing this formula does is check the date in column B against the start date:
=IF(B4>=start
If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the IF function runs this snippet:
(MOD(DATEDIF(start,B4,"m")+n,n)=0)*value
Inside MOD, the DATEDIF function is used to get the number of months between the start date and the date in B4. When the date in B4 equals the start date, DATEDIF returns zero. On the next month, DATEDIF returns 1, and so on.
To this result, we add the value for the named range “n”, which is 3 in the example. This effectively starts the numbering pattern at 3 instead of zero.
The MOD function is used to check each value, with n as the divisor:
MOD(DATEDIF(start,B4,"m")+n,n)=0
If the remainder is zero, we are working with a month that requires a value. Instead of nesting another IF function, we use boolean logic to multiply the result of the expression above by “value”.
In months where there should be a value, MOD returns zero, the expression is TRUE, and value is returned. In other months, MOD returns a non-zero result, the expression is FALSE, and the value is forced to zero.
