This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice.
Example
In this example we have a list of email addresses with a Y or N in the next column depending on if the person is planning to attend the national dinosaur convention. Now we want to get a comma separated list of the email addresses for those who plan to attend. We use the formula:
title: “How To Conditionally Concatenate A Range” ShowToc: true date: “2022-12-31” author: “Aaron Burke”
Example
Generic Formula
What It Does
This formula will conditionally concatenate a range based on a criteria in another range.
How It Works
IF(ConditionRange=Condition,Range,””) will create an array containing data from the Range when it meets the given condition. In our example this will create the following array.
TEXTJOIN(Delimiter,TRUE,Array) will concatenate the individual items in the Array and separating them with the chosen Delimiter. Using TRUE as the middle argument will skip any blank cells in the array. In our example TEXTJOIN(“”,TRUE,{“”;”E”;”X”;””;”C”;””;”E”;”L”;””}) results in EXCEL!
