In our previous tutorial, we were looking at Excel If contains formulas that return some value to another column if a target cell contains a given value. Aside from that, what else can you do if a cell contains specific text or number? A variety of things such as counting or summing cells, highlighting, removing or copying entire rows, and more.
- Count if cell contains - formula examples
- Count if cell contains any text
- Count if cell contains specific text
- Count if cell contains text string (partial math)
- Count if cell contains multiple substrings
- Count if cell contains number
- Sum if cell contains text
- Perform different calculations based on cell value
- Excel conditional formatting if cell contains
- If cell contains certain text, delete row
- If cell contains, select or copy entire rows
Excel 'Count if cell contains' formula examples
In Microsoft Excel, there are two functions to count cells based on their values, COUNTIF and COUNTIFS. These functions cover most, though not all, scenarios. The below examples will teach you how to choose an appropriate Count if cell contains formula for your particular task.
Count if cell contains any text
In situations when you want to count cells containing any text, use the asterisk wildcard character as the criteria in your COUNTIF formula:
COUNTIF(range,"*")
Or, use the SUMPRODUCT function in combination with ISTEXT:
SUMPRODUCT(--(ISTEX(range)))
In the second formula, the ISTEXT function evaluates each cell in the specified range and returns an array of TRUE (text) and FALSE (not text) values; the double unary operator (--) coerces TRUE and FALSE into 1's and 0's; and SUMPRODUCT adds up the numbers.
As shown in the screenshot below, both formulas yield the same result:
=COUNTIF(A2:A10,"*")
=SUMPRODUCT(--(ISTEXT(A2:A10)))
You may also want to look at how to count non-empty cells in Excel.
Count if cell contains specific text
To count cells that contain specific text, use a simple COUNTIF formula like shown below, where range is the cells to check and text is the text string to search for or a reference to the cell containing the text string.
COUNTIF(range,"text")
For example, to count cells in the range A2:A10 that contain the word "dress", use this formula:
=COUNTIF(A2:A10, "dress")
Or the one shown in the screenshot:
You can find more formulas examples here: How to count cells with text in Excel: any, specific, filtered cells.
Count if cell contains text (partial match)
To count cells that contain a certain substring, use the COUNTIF function with the asterisk wildcard character (*).
For example, to count how many cells in column A contain "dress" as part of their contents, use this formula:
=COUNTIF(A2:A10,"*dress*")
Or, type the desired text in some cell and concatenate that cell with the wildcard characters:
=COUNTIF(A2:A10,"*"&D1&"*")
For more information, please see: COUNTIF formulas with partial match.
Count if cell contains multiple substrings (AND logic)
To count cells with multiple conditions, use the COUNTIFS function. Excel COUNTIFS can handle up to 127 range/criteria pairs, and only cells that meet all of the specified conditions will be counted.
For example, to find out how many cells in column A contain "dress" AND "blue", use one of the following formulas:
=COUNTIFS(A2:A10,"*dress*", A2:A10,"*blue*")
Or
=COUNTIFS(A2:A10,"*"&D1&"*", A2:A10,"*"&D2&"*")
Count if cell contains number
The formula to count cells with numbers is the simplest formula one could imagine:
COUNT(range)
Please keep in mind that the COUNT function in Excel counts cells containing any numeric value including numbers, dates and times, because in terms of Excel the last two are also numbers.
In our case, the formula goes as follows:
=COUNT(A2:A10)
To count cells that DO NOT contain numbers, use the SUMPRODUCT function together with ISNUMBER and NOT:
=SUMPRODUCT(--NOT(ISNUMBER(A2:A10)))
Sum if cell contains text
If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function.
For example, to find out how many dresses are in stock, use this formula:
=SUMIF(A2:A10,"*dress*",B2:B10)
Where A2:A10 are the text values to check and B2:B10 are the numbers to sum.
Or, put the substring of interest in some cell (E1), and reference that cell in your formula, as shown in the screenshot below:
To sum with multiple criteria, use the SUMIFS function.
For instance, to find out how many blue dresses are available, go with this formula:
=SUMIFS(B2:B10, A2:A10,"*dress*",A2:A10,"*blue*")
Or use this one:
=SUMIFS(B2:B10, A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")
Where A2:A10 are the cells to check and B2:B10 are the cells to sum.
Perform different calculations based on cell value
In our last tutorial, we discussed three different formulas to test multiple conditions and return different values depending on the results of those tests. And now, let's see how you can perform different calculations depending on the value in a target cell.
Supposing you have sales numbers in column B and want to calculate bonuses based on those numbers: if a sale is over $300, the bonus is 10%; for sales between $201 and $300 the bonus is 7%; for sales between $101 and $200 the bonus is 5%, and no bonus for under $100 sales.
To have it done, simply multiply the sales (B2) by a corresponding percentage. How do you know which percentage to multiply by? By testing different conditions with nested IFs:
=B2*IF(B2>=300,10%, IF(B2>=200,7%, IF(B2>=100,5%,0)))
In real-life worksheets, it may be more convenient to input percentages in separate cells and reference those cells in your formula:
=B2*IF(B2>=300,$F$5,IF(B2>=200,$F$4,IF(B2>=100,$F$3,$F$2)))
The key thing is fixing the bonus cells' references with the $ sign to prevent them from changing when you copy the formula down the column.
Excel conditional formatting if cell contains specific text
If you want to highlight cells with certain text, set up an Excel conditional formatting rule based on one of the following formulas.
Case-insensitive:
SEARCH("text", topmost_cell)>0
Case-sensitive:
FIND("text", topmost_cell)>0
For example, to highlight SKUs that contain the words "dress", make a conditional formatting rule with the below formula and apply it to as many cells in column A as you need beginning with cell A2:
=SEARCH("dress", A2)>0
Excel conditional formatting formula: if cell contains text (multiple conditions)
To highlight cells that contain two or more text strings, nest several Search functions within an AND formula. For example, to highlight "blue dress" cells, create a rule based on this formula:
=AND(SEARCH("dress", A2)>0, SEARCH("blue", A2)>0)
For the detailed steps, please see How to create a conditional formatting rule with a formula.
If cell contains certain text, remove entire row
In case you want to delete rows containing specific text, use Excel's Find and Replace feature in this way:
- Select all cells you want to check.
- Press Ctrl + F to open the Find and Replace dialog box.
- In the Find what box, type the text or number you are looking for, and click the Find All
- Click on any search result, and then press Ctrl + A to select all.
- Click the Close button to close the Find and Replace
- Press Ctrl and the minus button at the same time (Ctrl -), which is the Excel shortcut for Delete.
- In the Delete dialog box, select Entire row, and click OK. Done!
In the screenshot below, we are deleting rows containing "dress":
If cell contains, select or copy entire rows
In situations when you want to select or copy rows with relevant data, use Excel's AutoFilter to filter such rows. After that, press Ctrl + A to select the filtered data, Ctrl+C to copy it, and Ctrl+V to paste the data to another location.
To filter cells with two or more criteria, use Advanced Filter to find such cells, and then copy the entire rows with the results or extract only specific columns.
This is how you manipulate cells based on their value in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook
Excel If Cell Contains Then - examples (.xlsx file)
You may also be interested in
- How to AutoSum in Excel
- Using logical functions in Excel: AND, OR, XOR and NOT
- How to calculate running total (cumulative sum) in Excel
- How to count text characters in Excel cells
FAQs
How do I make an IF then formula in Excel? ›
Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. For example: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")
How to sum values based on criteria in another column in Excel? ›If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
How do you sum in Excel if a cell contains specific text? ›Sum if cell contains text
If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function. Where A2:A10 are the text values to check and B2:B10 are the numbers to sum. To sum with multiple criteria, use the SUMIFS function.
2.1) In the Lookup and Sum Type section, select Lookup and sum matched values(s) in row(s) option; 2.2) In the Select Range section, specify the Lookup Value, Output Range as well as the Data table range; 2.3) In the Options section, choose the Return the sum of all matched values option; 2.4) Click OK.
Can I use an IF and then formula in conditional formatting? ›But in conditional formatting, IF/THEN/ELSE syntax cannot be applied in a single rule. Conditional formatting is applied using IF/THEN logical test only. It must return TRUE for conditional formatting to be applied.
How do you write an if then statement? ›Another way to define a conditional statement is to say, “If this happens, then that will happen.” The hypothesis is the first, or “if,” part of a conditional statement. The conclusion is the second, or “then,” part of a conditional statement. The conclusion is the result of a hypothesis.