The IF function features in many of the advanced formulas written on Google Sheets. Today we will combine it with other functions to create IF CONTAINS functions that lets us SUM and COUNT number of cells IF they contain a specific text.
To create our IF CONTAINS function we will be using the IF, REGEXMATCH, COUNTIF and SUMIF functions.
If you would like to follow along with the examples we are going to work through, here is a link to the sheet. To make your own editable version, go to File > Make a copy.
Example #1 - IF CONTAINS
Now onto the first example: We are going to create an IF CONTAINS function to determine whether each of the bakery items is a shortbread or not.
In cell B4 we can use the REGEXMATCH function to check whether the cell we're checking contains the text "Shortbread". The formula looks like this:
REGEXMATCH takes two parameters, text and regular expression. The text we are checking if there is a match for is in cell A4 and the regular expression we are checking against is "Shortbread" in cell B1. We have used an absolute reference for the regular expression so that is stays constant as we copy the formula down the list.
The combined formula looks for the matching text and returns "Yes" if it is there and "No" if it isn't. In our example data, both B6 and B6 return "Yes".
Example #2 - IF CONTAINS Then COUNT
To count the number of cells that contain our string we can combine IF with the COUNT function. Here we will look for text matches and add them to a running counter if they do. The formula looks like this:
The COUNTIF function takes two parameters, and in our case the range that we want to test against the criterion is B4:B8 and the criterion is "Shortbread" in cell B1. The number 2 is returned because there are two shortbreads in the list.
Example #3 - IF CONTAINS Then SUM
If we have a list items with numeric values alongside it we can use the SUMIF function to add up how many of a particular item there are.
To add up the number of shortbreads sold, we need to input three parameters into the SUMIF function. The range to be tested against the criterion is A4:A8, B1 contains the criterion we're testing against and B4:B8 contains the range to be summed if a cell in the range matches the criterion. The formula looks like this:
So there it is, those are the fastest ways we can create IF CONTAINS functions to count and sum ranges if they contains particular values. If you had any trouble following the examples, be sure to check out the example sheet.
If you have any comments for us, or if you have any requests as to what we can cover in future articles, please leave them in the section below - we would love to hear from you.
