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)))
Formulas to count cells containing any text

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:
Formula to count cells containing specific text

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&"*")
Count cells that contain a specific substring (partial match)

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 cells that meet both of the specified conditions.

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)))
Formulas to count cells that contain or do not contain numbers

For more examples, see Excel formulas to count cells with certain 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.

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:
If a cell contains specific text, sum numbers in another column

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.
Sum cells with multiple criteria

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.
Perform different calculations based on a cell value

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 specific text

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)
Excel conditional formatting formula: if cell contains with multiple conditions

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 a cell contains certain text, remove the entire row.

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)

Table of contents

244 comments

  1. I want to find the $$ sold of each pattern in a list of products. Column B contains the description, a string of characters that also contains a pattern name. Column E contains the $$ sold of each product. In Column F I have listed each pattern name, and I would like Column G to calculate the total $$ sold in each pattern. I just can't figure out how to do this.

      • Thanks for answering, here's a sample of the data:

        Col A (description):
        Bee Cheese Knife
        Bottle Opener Stag
        Mini Pheasant Stopper
        Antler Opener
        Coasters Antler
        Bee Beer Glass
        Stag Placemat
        2 Round Stag Glasses

        Col B (qty sold)
        12
        6
        9
        18
        7
        8
        12
        10

        Col C ($ sold)
        $375
        $127
        $3987
        $20
        $987
        $324
        $777
        $987

        Then I have the pattern name list:
        Bee
        Stag
        Pheasant
        Antler

        Next to each pattern name, I want to sum the qty and the $ , so for example, the output For "Bee" would be qty 20, $699.

  2. I need a formula that looks at Cell A1, which contains Account number then finds the same Account number in A1:A200 and then gives me the corresponding data from B1:B200

  3. I am looking to calculate from a spreadsheet, a win-loss record for a team based on the day of the week. If cells a2-a100 represent the day of the week, and cells f2-f100 represent win or loss, how would i write the formula to calculate the winning percentage of all games played on saturday?

  4. Hi,
    I need to work out how to Sum values when they are coded in a different column e.g.:

    Column A has codes:
    4201
    4605
    4807
    4201
    4598
    4605
    4201

    Column B has £ values against those codes.

    I need to find a way to sum the values in column B each time 4201 (for example) occurs in Column A without having to add it up each time. Is there a way to do this?

  5. Having trouble figuring out a solution. I have two (2) columns with a number of rows (likely a few more than shown below). I need to find text/data matches in column A (ENTRANT), add the respective POINTS from the same row(s) in column B together, and then produce a top five (5) in POINTS where the ENTRANT name and total POINTS are provided by the function/calculation. I'm fine if the results use all ENTRANT names in the Table but I only truly need the top five (5). But I need the results to be in order by POINTS with the highest number at the top. The ENTRANT names will change every so often so I cannot have the function etc searching for a specific name; the names have to be located and matched up (if that makes sense). Also, if POINTS totals are equal, then I don't really care if the names are then alphabetical (but it wouldn't hurt); I'm good either way.

    Sample data in [A1:B15] with HEADER ROW in Table.

    ENTRANT POINTS
    Tyler 5
    David 5
    Zac 3
    Josh 3
    Tyler 5
    Mark 5
    Andy 3
    Dale 3
    Tim 15
    Karl 10
    Jacob 5
    Tyler 15
    Dale 10
    Jacob 5

    The results should display similar to the following:

    Tyler 25
    Tim 15
    Dale 13
    Karl 10
    Jacob 8

    • Hello!
      To get a list that is sorted by points, you can get a list of names using the UNUQUE function, calculate the sum of points for each name using the COUNTIF function, and then combine these values into one array using the HSTACK function:

      =SORT(HSTACK(UNIQUE($A$1:$A$14),SUMIF($A$1:$A$14,UNIQUE($A$1:$A$14),$B$1:$B$14)),2,-1)

      To extract the first 5 rows from this array, use TAKE function :

      =TAKE(SORT(HSTACK(UNIQUE($A$1:$A$14),SUMIF($A$1:$A$14,UNIQUE($A$1:$A$14),$B$1:$B$14)),2,-1),5)

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  6. I have a small set of non-linear tabular data that spans vertically and horizontally. Rows are actually based on dates of a month while the columns do not have any kind of headings. Each entry occupies two cells in a row e.g. [A1:date][B1:amount][C1:label][D1:amount][E1:label]. I wanted to sum up amounts in the cells that contain a certain label in the corresponding cell. SUMIFS seemed to be a good function but it only allows multiple ranges for criteria but not for the sum ranges.

    Sample data in [A1:G2]

    1-May-23 | 300 | ABC | 200 | DEF | 100 | XYZ
    2-May-23 | 100 | EDX | 400 | GHL | 100 | ABC

    Problem: SUM amounts that have ABC in corresponding cells.

    Thanks

  7. Hello,

    I have data in column A that has a variety of different variables (ABC, GHY, DUY, IKL, ABC, ABC, DUY, LJK, ABC, IOP, ABC). I have varied dollar amounts in column D that correspond with column A. I want to add all the dollar amounts that are associated with ABC. I have tried but cannot figure this out.

  8. I have a spreadsheet laid out like this. I have been summing the batch total by manually selecting the amount with that batch number, but is there a function to use? Sometimes amounts in the same batch are dozens of lines down, and this becomes tedious.

    Amount Batch Number Batch Total
    35.00 10657 77.00
    36.00 11204
    71.00

    42.00 10657 91.00
    55.00 11204
    97.00

  9. Hello. I have a template that I am trying to build to track invoices for clients. For example, I want the template to be able to have an input tab where I put Dr Smith in column A and in column B $700 (who the bill is from and how much they spent) there will be multiple entries for each of my clients. I want to create a summary page that will look at the input tab and be able to list out all the bills (Dr Smith, Dr Marvin, General Hospital etc.) and the total amount they spent at each place. There will be multiple entries for each place that they spend money at, so there may be 5 entries for Walmart and 7 entries for Target. I want to create an itemized list on my input tab and on my summary tab have a formula that will populate the names and sum the amounts. I am not sure if this can be done. Any help would be greatly appreciated.

    INPUT TAB WOULD LOOK LIKE THIS

    DR SMITH $700
    DR KATZ $500
    DR KATZ $350
    DR DOE $200
    DR KATZ $250
    DR KING $300
    VA HOSPITAL $100
    VA HOSPITAL $250

    SUMMARY TAB WOULD LOOK LIKE THIS

    Dr Smith $700
    Dr Katz $1,100
    Dr Doe $200
    Dr King $300
    VA Hospital $350

  10. I would appreciate any help
    I have a spreadsheet with scores from events. Each person has participated in four events and they have four separated scores. I have assigned a point system for scores (i.e a 9 would give someone 20 points). is there a formula(function) that can look at the four cells of scores, determine if they qualify for points assigned and sum them into one cell of total points?

  11. I have read ur example in "Perform different calculations based on cell value". Is there another simple way formula without using IF?
    Because i have many tier in "Bonus". it too long if using IF

  12. Hello,

    On our employee vacation tracker, I'm trying to add up any cells with a "V" value over multiple sheets. When I used the =COUNTIF(January!C7:AG7,"*V*") it works for that sheet, but I'm not sure what the formula is to add all the sheets for January to December for each employee. I've tried this =COUNTIF(January:December!C7:AG7,"V") but I get an error. Thanks in advance for your help!

  13. I have a table with data validation for the payment frequency to keep the spelling consistent.

    I have table columns: Category, Payment Frequency (look up column), Payment Amount, Monthly Amount (cell multiplied or divided in formula)
    Calculation: When I enter the payment frequency and the payment amount, I want to calculate the monthly payment amount.

    Payment Frequency is calculated: monthly (*1), yearly (/12), bimonthly (*6), biweekly (*26/12), quarterly (*4/12), One time payment (*0) [I am not sure how to make that fit with the other payment options...]

    Thanks :)

    • * Sorry, thought I had that clearer. The Monthly Amount is the cell in which I will insert the formula. The payment amount is the one multiplied/divided based on the payment frequency. The multiplier/divisor is in the brackets after the given payment frequency.

  14. I am trying to tally attendance at programs based on age groups and type of program. I'm having issues getting a 0 total of the Sum of 3 age groups if type column contains "X" text. Using similar =SUMIF(D2:D6,"On",A2:C6) I get 23 to display in formula's box, but for =SUMIF(D2:D6,"Off",A2:C6) I get zero instead of 15? Please help me find & fix my error.
    Sample of spreadsheet:
    A B C D
    R1 Juv Teen Adult On/Off Site
    R2 5 0 0 On
    R3 0 7 6 On
    R4 0 0 3 Off
    R5 10 0 2 Off
    R6 0 4 1 On

    On Site total = 23
    Off Site total = 15

  15. Hello,

    I'm stumped on auto populating a cell. What I want to input is if sheet 2, column A contains any of the same text as sheet 1, column A. Then sheet 2 column 3 will auto populate the same numbers that are showing on Sheet 1, column 3.

  16. HI, Im looking for help with formatting a cell. So I want cell $G$277 to show as cell multiplied by .9789 if cell $E$277 shows as "bio". could anyone tell me how to input this please? thanks

    • sorry i wrote this incorrectly i mean to type

      HI, Im looking for help with formatting a cell. So I want cell $G$277 to show as cell $H$277 multiplied by .9789 if cell $E$277 shows as "bio". could anyone tell me how to input this please? thanks

      • Hi, thanks for the reply. I know i cant change the value of a cell, im trying to populate a cell based on an equation/formula based on a result from another cell but i just dont know how to write the equation.

        I want for instance cell A1 to show the result of cell B1 multiplied by 0.9789 as the "true" if cell C1 has the word "Bio" in it, does that make sense? thanks

          • Hi, I've tried that but nothing happens in the cell even though C1 has the word in it and B1 has a number populated in it ready for the formula to make the result in cell A1

            thanks

  17. i have this formula in a column =IF(COUNTIFS($I:$I,I2,$Z:$Z,"YES"),"OK","NO") the "YES" result corresponds to another value as a result of look up on the other sheet. i would like to populate that lookup value on the entire next column.

    how to do it please. thanks in advance

      • hi sorry for that.. here in my table below, i have two groupings. In the column C is where my formula as =IF(COUNTIFS($A:$A,A2,$C:$C,"YES"),"OK","NO"). All the YES value in Column C ( as Apple and Milk ) is from my table in the other sheet with corresponding value of Fruits and Drinks. I need to put all the lookup result in the all cell for each group in Column E. how can i do it.

        Column A Column B Column C Column D Column E
        IMS Cherry No Ok Fruits
        IMS Apple Yes Ok Fruits
        IMS Soda No Ok Fruits
        IMS2 Soda No Ok Drinks
        IMS2 Milk Yes Ok Drinks
        IMS2 Orange No Ok Drinks

        • Hi!
          Your explanations are still not clear. I assume that your formula is not in C but in column D. This formula determines if there are duplicate rows. The results of this formula do not match with your data.
          Explain "lookup result" - what are you looking for and where.
          As it's currently written, it's hard to tell exactly what you're asking.

          • Hi!

            Apologies.. yes my formula is n Column D where it becomes "OK" as it found "Yes" in Column C.

            First is , i have to vlookup the DATA in Column B from the other sheet so i can have the YES or NO in Column C ( like the APPLE and Milk as YES value in Column C )

            Second, in Column D is where i have the IF formula whether "OK" or "NO" .

            Third, in Column E, something i need to get done. The YES value in Column C ( as APPLE and MILK corresponds to Fruit and Drink from other Sheet (so vlookup Apple will give me a result value of Fruit.)

            so here in Column E i need to have a condition that if after the vlookup, the result value ( as Fruit ) will be filled in the certain cell per Group ( 3 cell per Column A groupings as per my sample.)

            my apologies if i cant still explain it clearly here.. many thanks!

            • Hi!
              To check data from column B on another sheet, you can use the MATCH function like this:

              =IF(ISNUMBER(MATCH(Sheet1!B1,Sheet2!B1:B100,0)),"Yes","No")

              I don't understand what you want to do in column E.

              • Thank you ..

                i can check the data from column B on another sheet by the this formula

                =IF(ISNA(INDEX(Sheet2!B1:B2,MATCH(TRUE,INDEX(Sheet2!A1:A2='Sheet1'!B1,0),0))),"YES","NO")

                sheet2:
                Column A Column B
                Apple Fruits
                Milk Drinks

                so in Sheet1: Column E
                What i want in Column E is to fill the cell with "Fruits" without the #N/A

                and this is what i am having now in Column E (based on my table above)

                Column E
                #N/A
                Fruits
                #N/A
                #N/A
                Drinks

                instead i want to have the formula which will gives me this.

                Column E
                Fruits
                Fruits
                Fruits
                Drinks
                Drinks
                Drinks

  18. Need help writing a formula to calculate percentage of invoices validated with payment dates. Essentially, we want to write a formula to generate a count of the cells with dates in them, and to exclude the cells with nothing. Based off this, we are building a gauge chart to depict the percentage of invoices validated. Min value would = 0 and Max value would = total invoices (with & without dates) and this formula would be the main data point showing percentage of invoices ONLY with payment dates tied to them.

    Any tips/advice are greatly appreciated!

  19. I'm looking for a formula that counts the number of cells that contain ANY text, and sum the number of cells in a cell below.
    I need to sumerize how many clients each day, and obviously they all have diffrent names, and its always changing. As a bouns I'd like to enter that sum into another spreadsheet, on a specific days cell. the 2nd part is not vital. I'm running a homeless shelter and finding this formula stuff hard to absorb, lol.

    • So I figured the first part out, and feel free to ignore the second. I'm trying to learn everything and have a couple course type files, and now found this jem. Thanks for helping people out so much, very kind of you all.

  20. Hi.

    Is possible to sum all WA11?

    WA11 4
    AdBlue 1, WA11 2
    AdBlue 3, WA11 3, shift 4

    ... and everything is in one column

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)