Table of Contents
The Concatenate If Formula in Google Sheets allows you to join two or more strings together, based on certain criteria. It can be used to join text strings, numbers, and dates together. This can be useful for combining data from multiple cells into a single cell, and can also be used to format data into a specific format. The formula can be used with the AND, OR, and NOT functions to meet specific criteria. To use the Concatenate If Formula, the CONCAT, IF, and IFERROR functions are used. The CONCAT function combines the strings, the IF function sets the criteria, and the IFERROR function deals with any errors in the formula.
The following examples show how to use a concatenate if formula in Google Sheets.
Example 1: Concatenate If (By Column)
Suppose we have the following data in Google Sheets:
We can use the following formula to concatenate cells in column A and B only if the value in column B is equal to “Good”:
=CONCATENATE(IF(B2="Good", A2:B2, ""))
The following screenshot shows how to use this formula in practice:
If the cell in column B is equal to “Good” then the value in column C is equal to the concatenation of the cells in column A and B.
Otherwise, this formula simply returns a blank value.
Note: You can find the complete documentation for the CONCATENATE function in Google Sheets .
Example 2: Concatenate If (By Row)
Once again suppose we have the following data in Google Sheets:
We can use the following formula to concatenate all of the cells in column A where the value in column B is equal to “Good”:
=ARRAYFORMULA(TEXTJOIN("",FALSE,IF(B2:B7="Good",A2:A7,"")))
The following screenshot shows how to use this formula in practice:
Notice that the value in cell C2 is the result of concatenating every value in column A where the corresponding value in column B is equal to “Good.”