# Excel: If cell contains then count, sum, highlight, copy or delete

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.

## 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)))` ### 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: ### 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&"*")` ### 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:

1. Select all cells you want to check.
2. Press Ctrl + F to open the Find and Replace dialog box.
3. In the Find what box, type the text or number you are looking for, and click the Find All
4. Click on any search result, and then press Ctrl + A to select all.
5. Click the Close button to close the Find and Replace
6. Press Ctrl and the minus button at the same time (Ctrl -), which is the Excel shortcut for Delete.
7. 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.

For the detailed step-by-step instructions, please see the following tutorials:

This is how you manipulate cells based on their value in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample Excel If Cell Contains workbook. I thank you for reading and hope to see you on our blog next week!

## You may also be interested in:

### 28 Responses to "Excel: If cell contains then count, sum, highlight, copy or delete"

1. SALAMI QUDDUS TOSIN says:

How To Highlight Row If Cell Contains Text/Value/Blank In Excel

2. krishanu das says:

I would like to highlight Equal no. of cells to the numeric value in some cell. i.e
if i put 5 in cell no. A1, then cell B1,C1,D1,E1,F1 Shall be filled in red colour...
how to do it pls help

• Doug says:

Krishna Das:
Sorry, I think the only way to do what you want is by writing some VBA code. That's beyond the scope of this blog.

3. phil says:

How would I sum a filtered columns visible data by using another columns text, I need the below formula to count visible cells of a filtered column based on a word

=COUNTIF(A2:A10,"*dress*")

• Phil says:

Sorry, More specific. Same question

=SUMIF(E18:E4020,"Immediately",\$D18:\$D4020)/E2

Trying to get the above formula to sum Visible cells in column D, currently it sums all cells

Column E contains text,"Immediately" is one of the choices.
Column D contains numeric values that need to be summed
based on the choice of the word "Immediately"
They are filtered.

• Doug says:

Phil:
Svetlana has a very thorough article here on AbleBits that covers this topic.
Enter "sum only filtered (visible) cells" in the search box and you'll see the link to the article.

4. Phil says:

Thanks for responding Doug, but nothing there is helping this particular scenario.

5. PJ says:

=SUM(COUNTIFS(TAB!\$H:\$H,{"chips","ice cream"},TAB!\$E:\$E,">1/1/18",TAB!\$E:\$E,"<2/1/18"))

I am trying to modify this formula so that once the criteria is met, that all the numbers found in column U are added together. I can't seem to figure how to get this to work out. Please help.

6. sarah says:

Hello,

how to do if the cell is a date, say: 1/1/2018, then to sum a row of numbers and add another number in a cell, e.g. a2:j2, +e20. If it is not the date (1/1/2018), then let it be blanked...

thank you.

7. Candace says:

I’d like to use a sumifs type formula, but instead of summing numbers, I’d like to grab text (vlookup won’t work because I need to use multiple criteria). Any ideas?

8. Kevin says:

I would like to select only cell with numerical value from row containing numerical and non-numerical value.

9. P S Lim says:

What Excel function and how to use if I select a word in cell (with drop down list with few text) and I would like each selected text to return a different drop down list from different sheet?

10. Daniel Dicke says:

I was hoping you could help me with a formula.

I would like to calculate a range cells in a row if text is "Something"

so:

2.5 PM
2 CE
2.5CE
2 IPT
3.5 PM

11. Daniel Dicke says:

I was hoping you could help me with a formula.

I would like to calculate a range cells in a row if text is "Something"

so:
A1 A2 B4:
2.5 PM 6.0 This pulls all the values from A1 that have PM in A2.
2 CE
2.5 CE
2 IPT
3.5 PM

12. Doug says:

Daniel:
You will use the SUMIF function including the optional sum_range. Where the data is in columns J, K and L enter this in L2:
=SUMIF(K2:K5,"PM",J2:J5)
You're saying, "Sum the values in J2 thru J5 where the text in the adjacent cells K2 thru K5 is PM." So, it's range, criteria, sum_range.

13. jess says:

I need to do something along the lines of "If cell contains text "ab" then perform calculation (D10-0.15)-120)" basically I need the number showing in that cell to either be calculated using one formula or another..... not sure how to structure this or if it's possible. Thanks in advance!

14. Ray says:

A cell contains =IF(ISNUMBER(SEARCH("Avox Production",B60)),"1","")
The statement enters a "1" in the cell when the word "Avox Production" is typed in another cell B60.
I have a range of cells with the statement listed above. What I need to do is gather the sum of all cells with "1" and populate the total of these cells into another worksheet.
I've tried =SUM(B60:B65,Data!B60:B65) but nothing populates in the cell.

• David says:

What is the cell address for the cell containing the formula:
=IF(ISNUMBER(SEARCH("Avox Production",B60)),"1","")
That cell address will contain the "1" or ""
Sum that column to get your values

15. ROB D says:

How about if i have a column of numbers and i want to search for matching numbers that then contain text in another column that i want to highlight mismatched PART text string. i.e
A1 B1
1 1562544 Engine
2 1562544 Cam Shaft
3 1573333 Engine 2
4 1573333 Engine 2
5 1582444 Engine
6 1582444 Engine
7 1582444 Cam Shaft Fixing
8 1628738 Cam Shaft
9 1628738 Cam Shaft - Chrome
I would only want a return for the rows that contain conflicting information i.e 1562544 and 1582444 in this case, i do not mind if it is just rows 2 & 7 that are returned or all that mismatch i.e 1&2 and 5,6&7 . Worth noting that rows 8&9 i do not consider a mismatch (hence the need for a PART string match)
Hope that makes sense

16. sarmad azim says:

All Example and conversation are helpful,but if u add language option in your web side so its so easy and convenient for me and others, and I think Its plus point about you..
kind regards

17. David Lee says:

I'm working on a spreadsheet where i would like for a formula to be created when any text is entered into the cell to the left. If you enter a company name in a cell it will then create a PO based off the job number (in one cell) & the cost code (another cell)
Cell B2 is My Job Number & Cell A14 is My Cost Code. =B2&-A15 give me a my PO (Job Number-Cost Code). Now i would like this to be created only when text is entered in the cell to the left.

18. nick c says:

I am trying to see if excel can search a column and automatically add up specific cells to come as close to without exceeding a specific number. Example: I have a 12' (120") piece of lumber and I need to cut that into as many posts as possible with little waste. So in the example below it would highlight cells (A1, A2, A5, A6) in a specific color, then in another color continue with the other cells (not previously selected) and perform the same function. Thanks for any help.
A1 13.5"
A2 25.75"
A3 54"
A4 32"
A5 16"
A6 64.75"
A7 10"
A8 12.5"
A9 11

19. Sri says:

C5=Q
D5=Q
E5=H
F5=H
G5=Q
H5=Q
I5=H
J5=Q
K5=H
L5=H
M5=H
N5=H
O5=Q
P5=Q
Q is the travel plan for onsite. for the above example its 7 weeks stays.
but the onsite trip would be 4. so i need to caluclate the trip count.
I want to have a successive cell count where the value is "Q"
For the above example it would be 4.
Thanks
Srini

20. Simon Hunt says:

Trying to make a formula where if a cell has the text "GMP 1" then show the value which is in the same row but from column A. Also this has to work over separate sheets.
So, for example, i need it to look through column C on sheet 1 for the word "GMP 1" but return the row value of Column A, and put the result on sheet 2. Currently i have multiple efforts at the formula and getting results where it adds up the values from A (which i dont want) or #value #n/a or just the count value of if it is true. Tried multiple different starting points (sumproducts/vlookup etc)

21. Eric Moore says:

Any Help Apprecaiated:
Essentially I am trying to write a formula where if all cells in a column range contain a numerical value it will SUM them, but add a condition where if the column contains a text it will search for the text and instead of returning a SUM it will return "Not Complete".

Currently I am working with the following formula but cant seem to get it to work:
=IF(ISNUMBER(SEARCH("Not Complete",H4:\$H2000)),"Not Complete",SUM(H4:\$H2000))

For Reference:
Column of values mixed with text containing "Not Complete"
\$0.052000
\$0.052000
\$0.052000
\$0.052000
Not Complete
Not Complete
Not Complete
Not Complete
\$0.052000
\$0.052000
\$0.052000

22. Dinesh Kommireddy says:

Hi,
Is there a solution for this?

Basically, I have month in one column and dollars in another. If I want the sum of all the dollars in June, July and Aaugust in a separate column. Is there a formula that does this. I am sure there is but cannot figure it out.

Thanks,
Dinesh.

Month dollar sum
June 20
June 30
June 25
June 22
June 31 sum of June dollars
July 18
July 16
July 15
July 22
July 19 sum of July dollars
August 6
August 3
August 32
August 34
August 24 sum of Aug dollars

23. Scott says:

I need a formula that will search a specific cell (E2) or all of column E for a "key" word and then if found, take the date in A2 and add 30 days to it and put this all in B2.

Please let me know if this can be accomplished.
Thanks.

24. Eddie says:

How do I have a cell calculate a sum if there's a certain word or text in another cell? I.E. 12345*2% will only be calculated if say cell A5 was showing the word "No".
Thanks!

60+ professional tools for Excel