Comments on: 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 the target cell contains a specific value. Aside from that, what else can you do if a cell contains certain text or number? Continue reading →

Comments page 4. Total comments: 248

  1. Screenshot link to reference my sheet: https://gyazo.com/36d77c6c536984cfe9b56d1bc51b6e09

    Hi, my business gives commissions to our drivers (called “captains” as shown in the Excel screenshot). Their commission is 10% of the sale price of a surcharge they sell.

    I want to track their daily commissions per captain and then total each daily commission for their weekly total commission per captain.

    I already have their daily tracking set up to automatically calculate their daily commissions based on the prices of surcharges and tally of quantity sold for each.

    My issue now is making the formula that automatically searches row P5 to T5 for instances of their unique name being written. Each time their name is written in that row indicates one working day with commissions made (total for each daily commission is in row P30 to T30) in their own column.

    I would then like the same formula to have N34 to N36 autofill if there are captain’s names found in P5 to T5 (without duplicating their names) and then totaling their daily commissions specific to each captain’s name in the weekly totals at O34 to O36.

    I hope this makes sense. Let me know if you need clarification.

    I’ve tried for several hours to figure it out with various formulas but I’m not too versed in Excel so I’m hoping someone can help me out. Thanks in advance!

  2. Hi,
    SUMIF function is working for me but SUMIFS is not working even though I tried so many times. =SUMIFS(J2:J29,G2:G29,"Passive",G2:G29,"Connector") is the formula which I am trying in my excel. I also have used * prior to text values but no result and instead its showing 0. I can't understand why its showing 0. Kindly help.

  3. I have an Excel sheet where in column A i have various dates for sales over a three year period and in column B I have another column which contains text relating to the type of sale, either "Direct" or "Indirect" to indicate if the sale was a direct sale or through another party. I want to be able to count how many Direct or Indirect sales were made in each year. Can you help? I have tried combining the COUNTIF and COUNTIFS formulas but it's not working. I can get the number of sales in each year by using the COUNTIFS formula but the problem starts when I try to extract the count in each year for each of Direct or Indirect by combining the two formulas. Thanks! Your website is amazing! I've managed to solve a lot of problems by checking your solutions.

  4. Hello,

    What formula could I use to add up both numbers and letters in the same column? For example, I have 12 cells with values in the same column (the "x" respresents 1):

    10
    x
    x
    2
    3
    x
    x
    x
    x
    x
    x
    x

    Before the numbers were added and we were only counting "x", we used this formula =COUNTIF(G73:G85,"=x")

    Now that numbers are also included, I am having trouble finding a formula that adds both "x" as 1 and the rest of the numbers.

    Thank you!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(A1:A10)+COUNTIF(A1:A10,"x")

      SUM function only adds numbers and ignores text.

  5. Hi,

    I have two tabs:

    First tab:
    Column A Name
    Column B Yes ( or No)

    For example :
    Column A Column B
    Anna Yes
    Lily No

    If column B is value is Yes, I want to add column A value (Anna) to the other tab A1.
    If column B is value is No, I don't want to add column A value (Lily) into the other tab.

    How to achieve that?

    Thanks,

  6. I am having a really hard time trying to get the formula I need. The spreadsheet is for stock options. For easy example, column A are debits that it cost to open the contract. The adjacent cell in column B is the price I sell to close the contract. I have everything I need for the gain/loss and percentage taken. I am trying to figure out how I would calculate my true return? if for example I open a contract for -$500 and sell it for $750, I have a return of 50% on that specific row. However if I open another trade using that same $500 and then the $250 I captured for a trade as well, how would I calculate my overall return? It would want to calculate my return on $1250 instead of $750. Any help would be greatly appreciated.

    1. Hi!
      We are not stock specialists. If you have a specific question about the Excel formula, we will try to help.

  7. Trying to make a formula using sumifs in B1 "lol" && C1 "wow

    A1 blank cell (i want to put name text here which will be the reference of A2:A10)
    B1 show the total lol
    C1 show the total wow
    A2:A10 Names
    B2:B10 [sum_range]
    C2:C10 text "Lol" or "wow"

    I want to sum the value in B2:B10 if it meets the condition:
    Sumif:
    Condition 1 If A2:A10 is true (or same with the text I input in A1)
    Condition 2 If C2:C10 "lol" or in "wow" category

    e.q.
    A1 "10/06"
    B1 (if A1 is true A2;A10 sum the total 10/06 with lol
    C1 sum the total 10/06 wow

  8. Hey Alex,
    At the outset, very wonderfull site and informative.
    I have the data in the following cols
    Col A Col B
    21-Sep-21 55
    23-Sep-21 74
    02-Oct-21 21
    05-Oct-21 05

    All I want to do is add the values apprearing for Sep and Oct as below
    Sep'21 xxxxx
    Oct'21 yyy

  9. I am trying to find a formula to calculate cum GPA but will subtract the grades that have "TR" by it in the semester column. What formula if any would work for this?
    EXAMPLE

    Grade Hrs Points Semester
    A 3 12 SP20
    B 3 9 TR
    C 2 4 FA21
    D 1 1 TR

  10. I need help with this formula. =SUMIF($D:$D,"*Wave 1*",$F:$F)
    Right now I want to add values from F if D says Wave 1 however if the the sum is 0 i want it to say TBD .. not sure how to do the TBD part

  11. Hi,
    I need to add values in alternative cells. eg: values in I5,K5,M5 and the sum should ignore text in those cells. Please suggest

  12. In fact i would like to know whether excel can display the value of a cell in another cell by using an IF condition.
    e.g value of A1 is "FLOWERS"
    input text "FL" in B1
    the result should be the value of A1 to be displayed in C1 if you put a condition that if the value of B1 = "FL"
    is there any formula to solve this.

    thanks

  13. I have a list of parts in column A, then i have a list of dates in column B
    I want the formula to return how many times each part number has a date beside it
    123456 1-jan-21
    234567 5-jan-21
    123456
    123456 5-feb-21

    so i would want this to return
    123456 2
    234567 1

  14. Please help me figure this out. I have a spreadsheet of pupils' scores on a test. I have put a '1' in a cell if they got the question correct, a '0' if incorrect and a '.' if they didn't attempt it. I have a separate list to the side which says the name of the topic next to each question number. I would like to generate a list of topics they need to practice for each pupil based on the questions they got wrong. How can I do this? For example, if they got question 8 and 10 incorrect and these questions were a multiplication and a division question I would like a list which says multiplication and division. Please help me figure this out!

    1. Hello!
      Please use the following array formula -

      {=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($I3=$A$3:$A$30))*(--($J3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), COLUMN()-10)),"")}

      A3:C30 - your table, I3 - name, J3 - zero or "." K3 - this formula.
      Please have a look at this article - Vlookup multiple matches and return results in a row (Formula 2)
      I hope I answered your question. If something is still unclear, please feel free to ask.

  15. Hi Everyone, I would really like some help with this logic

    I would like to create a formula in cell C2, if D2 = text "X" perform sum of A2 + B2, but if D2 = text "W" cell = just the value in B2

    I hope this makes sense, TIA

  16. If all my cells contains value,(without blank) then I should get the output as T else F

    Will anyone please help me out

  17. What I'm looking for is a formula that will do the following:
    If cell a1 contains specific text, then count cell a2.

    I've tried all sorts of different options, but can't get it to show the right count.

      1. Here is an example of what I've attempting. Perhaps this might help.

        If in the range of data, X appears, then count the next cell after X (which would have A, B, or C in it).

  18. Thank you for all the Information they are great. what i would like to dois:
    I Have in column A a data validation that has X,Y Z and on a different sheet under X i have a list of names in a column as well as under Y ans Z. what I want, is when I choose for example X in column A, I want in column B automatically to copy all the names of the other sheet under X into the cells in column B into a column not row? please can you help me with this problem?

  19. Hello, could you please help me to find formula that highlights the cell with specific text when the text is a list? Is this the correct way to do it in Conditional Formatting (by formula)?
    =ISNUMBER(MATCH($A$2,List,0))
    when list is the range of list cells?
    If this is the best way, Is there a way to copy this exact formatting to other cells (format painter doesn't do the job)?
    Thank you so much in advance.

    1. Hello Ludmila!
      If I understand your task correctly, the following conditional formatting formula should work for you:

      =ISNUMBER(MATCH($A$2,F2,0))

      where F2 is the first cell of the list cell range.
      I hope it’ll be helpful.

      1. Hello Alexander, thank you for your answer. Unfortunately, the formula you've suggested didn't work for me, though the formula that includes range of cells containing the list works well.
        My additional question was is there a way to apply the same conditional formatting formula to multiple cells. I mean is there a fast way to copy the conditional formatting formula that will be updated to other cells (eg A3, A4 and so on) instead of creating a new rule for every next cell? Thank you!

        1. Hello!
          I'm sorry the formula wasn't useful to you. Apparently, we represent your data differently.
          How to copy conditional formatting is described here.

  20. I am looking to use a condition on one cell (A2=Kabul, then pick data from another sheet, K5) and the same condition will be repeated in one formula (A2=Laghman, A2=Logar..... and then data will be picked from a different cell). How I can do it?

    1. Hello Ebadullah!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

  21. Hi there,
    How do I Sum out multiple SKUs when I keyed them in according to the warehouse racking layout?
    Example :
    A1 B1 C1 are the name of the SKUs, while A1 C1 has the same SKU.
    A2 B2 C2 will be the Quantity of each SKU above.
    How do I get the the sum of A2 and C2 in a separate list if we got 600 SKUs.
    Thank you.

  22. Please if cell b4 has a data of 24pcs and cell c4 has 34000....
    I want to multiply the two cell together...
    Please how can I go about this.
    Thanks

    1. Hello Gbenga!
      If I understand your task correctly, the following formula should work for you

      =LEFT(A1,LEN(A1) - FIND("pcs",A1)) * B1

      Hope you’ll find this information helpful.

  23. Hello George!
    In order to extract all the characters after "UL9", use the formula below:
    =RIGHT(A10, LEN(A1) - FIND("UL9", A1,1) +1)

    As for pulling the text before "UL9", here is the formula for this case:
    =LEFT(A1, FIND("UL9", A1 ,1) - 1)

    BTW, there is a formula-free solution for this task called Extract Text tool. Check out its manual, I believe you'll find it helpful: https://www.ablebits.com/docs/excel-extract-text/

  24. How can I delete upon scanning a bar code, everything except the text that starts with UL9C0037365 value in a cell
    01008568230065341119071521UL9C0037365

    Thank you
    george

  25. Thank you so much for most helpful forumula

  26. 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!

  27. 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.

    1. Hello,

      Did you find a solution for your issue? I am currently trying to find something similar. I need to find all people who are online and add their hours, ignoring those who are on leave etc, Can anyone help?
      Thanks in advance

  28. 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

  29. 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

  30. 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)

    1. Did anyone ever answer you? I'm looking for the same solution and can't figure it out for the life of me. Thanks!

  31. 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

  32. 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

  33. 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.

  34. 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
    sarmad azim

  35. 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

  36. 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.

    1. 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

  37. 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!

  38. 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.

  39. 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

  40. 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

  41. 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?

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

  43. 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?

  44. 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.

  45. =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.

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

  47. 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*")

    1. 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.

      1. 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.

  48. 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

    1. 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.

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

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 :)