This is an array formula and must be entered with control + shift + enter.
At a high level, this formula uses a nested IF function to return an array of holidays between two dates. This array is then processed by the TEXTJOIN function, which converts the array to text using a comma as the delimiter.
Working from the inside out, we generate the array of matching holidays using a nested IF:
If the dates in B4:B12 are greater than or equal the start date in F5, and if the dates in B4:B12 are less than or equal the end date in F6, then IF returns a an array of holidays. In the example shown, the list looks like this:
This array is then delivered to the TEXTJOIN function as the text1 argument, where the delimiter is set to ", " and ignore_empty is TRUE. The TEXT JOIN function processes the items in the array and returns a string where every non-empty item is separated by a comma plus space.
Note: the TEXTJOIN function is a new function available in Office 365 and Excel 2019.
This formula uses two expressions in a single array inside the SUMPRODUCT function. The first expression tests every holiday date to see if it's greater than or equal to the start date in F5: ( B4:B12 >= F5 ) This returns an array of TRUE/FALSE...
The Excel TEXTJOIN function concatenates multiple values together with or without a delimiter. TEXTJOIN can concatenate values provided as cell references, ranges, or constants, and can optionally ignore...
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.