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 2. Total comments: 163

  1. Hi there,
    I have columns
    A B C D E F G H I
    Pay# Milestone Date Amount Paid On Pay# Milestone Date Amount Paid On TOTAL PAID
    1 2023-01-23 5000 2023-02-18 2 2023-10-03 3000

    I would like to know how can I create a formula to add the AMOUNTS from column c and g in the TOTAL PAID column (column I) just if the payment has a date in the PAID ON cells .

    Thank you

    1. Hi! I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Try this formula in column I:

      =IF(ISNUMBER(H2),G2+C2,0)

      I hope it’ll be helpful.

  2. Hello,

    I'm hoping you can help me - I would like an Excel formula to calculate the following for Gain/Loss:

    If cell F is greater than cell C, the answer in cell G is _______, but if cell C is greater than cell F, the answer in cell G is _________.

    Example:
    A B C D E F G H
    Quantity Bought Buy price Total Buy $ Quantity Sold Sell Price Total Sell $ Gain / Loss Running Gain/Loss
    100 $106.22 $10,622.00 100 $112.52 $11,252.00 $ 630.00 $630.00
    100 $ 89.34 $ 8,934.00 100 $ 78.56 $ 7,856.00 -$1,078.00 -$448.00

    Thank-you!
    Kasey

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

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

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

      1. this reply helped me a lot
        but after the equation of sum if shell i remove the duplication?
        or there another way more smart?

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

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

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

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

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

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

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

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

      1. Many thanks for your reply. i'll try look IFS function

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

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

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

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

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

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

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

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

            1. I need to do this through conditional formatting as there is other information inputted that i need it to ignore

              thanks

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

  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.

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

  21. I have a time sheet that has a job code that I would like excel to reference to add up the hours that each employee has worked for that job to come up with the total hours worked on that job for the week.

    So, if there is a letter in one cell, I want excel to take the numbers from another cell and add them all together for every occurrence of that letter in the table.

      1. I'm not sure that is the correct formula.

        Jobsite Code Job Total
        AB A
        CD C
        FG D

        Employee Mon Job Tue Job Wed Job
        John 6 A 8 C 7 D
        Joe 9 C 7 A 8 C

        Taking the table above, I want Excel to look for A and add the hours from the corresponding cell; so, there's an A in D7 and F8, I want Excel to add the hours in D6 (6 hours) and F7 (7) together for total hours worked on job A

  22. Hello.

      1. Thank you very much

  23. Hi i want to add following numbers which are present in a row

    23,45,#N/A, 56,#N/A, 756,...
    Please suggest the formula to solve it.

    1. I've got the same issue I think Baiju is having, in that Excel isn't clear on how you add up a series of columns or rows if some of the cells in the range have #NA or #NUM instead of a number.

      I've got someone's spreadsheet I'm trying to salvage. They're trying to track the total invoices they process each month, amount of days to process each one, the number that are late (>30 days), and the $ amount for the invoices that are late. So, I've been using =DATEDIF and =IF(AND and =COUNTIF formulas to take a stab.

      It's cumbersome but would be working, except their spreadsheet includes all their invoices, including those that haven't cleared yet. As a result I get #NUM! or #NA in those cells. When I try to sum the monetary values for the month (either =SUM or =COUNTIF) it fails because of the #NUM and #NA in the range. I haven't been able to find an explanation on what to do when you've got non-numbers in a column you're trying to add, so any advice you can provide would be appreciated. Thanks.

  24. I'm trying to make a formula in which if an exact value is can get 4 different values in 4 different cells . Eg if A1=4 then B2=12, C2=199, D2 =122,E2=78

    1. Hi!
      We wrote many times that an Excel formula can change the value only in the cell in which it is written. You need to use a VBA macro.
      Or use an IF formula in each cell.

  25. Hello,

    I'd really appreciate help with a formula.

    Column E is an IF statement and depending on a date range puts "YES" or "NO" in column E.

    I'm trying to get a separate cell to provide the SUM of the number of "YES"s. SUM and SUMIF do not appear to work. For example, I have attempted the formula =SUMIF(E2:E20,"YES"). It incorrectly provides "0" as the answer. Is this because Column E is a separate formula itself. How do I get around this?

    Your help is much appreciated.

    1. Hello!
      The SUMIF formula works with the results of formulas in the same way as with normal values. Your formula should work. Check what values are returned by your formulas in cells E2:E20. Perhaps there are extra spaces.

  26. Hello,

    I would appreciate your help on a formula.

    I am creating a vertical employee absence calendar in Excel (dates at the top, names on the side) with leave entered as text: holiday (H), half day holiday (HH), sick leave (S), and half day sick leave (SH).

    I would like to add a row total for each person.

    With COUNTA all entries = 1
    So for e.g. H + S + HH should add up to 2.5 but COUNTA shows 3.

    How can Excel add up different values for each type of text? So that that H = 1, HH = 0.5, S=1 and SH=0.5?

    Very grateful for your help
    Valerie

    Many thanks for your help!

    1. Hi there. I am trying to write a formula where I need to count how much three people spent.
      Name cost. Name total spent
      Me 300 me. ?
      You 150 you. ?
      Her 200 Her. ?
      Me 140
      You 200
      Her 500

  27. Hi

    how I sum if I had 3 table
    1 vendor (Samsung or Huawei)
    2 Cell mode (ex Samsung I9500 series)
    3 count

    I used below formula but it count other vendor model as well

    =SUMIF(J2:J23068,"*I9500*",K2:K23068)

    can you please support for the correction

      1. Hi
        I need your support for correction of formula.

        1. Please check below this formula is correct or not

          =SUM((Sheet2!$D$8:$QC$11)*(--(Sheet2!$C$8:$C$11='Cleaning details'!$D6))*(--(Sheet2!$D$4:$QC$4='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4)))+SUM(Sheet2!$D$12:$QC$15)*(--(Sheet2!$C$12:$C$15='Cleaning details'!$D6))*(--(Sheet2!$D$5:$QC$5='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4))+SUM(Sheet2!$D$16:$QC$19)*(--(Sheet2!$C$16:$C$19='Cleaning details'!$D6))*(--(Sheet2!$D$6:$QC$6='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4))+SUM(Sheet2!$D$20:$QC$23)*(--(Sheet2!$C$20:$C$23='Cleaning details'!$D6))*(--(Sheet2!$D$7:$QC$7='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4))

          1. Because this is not working in single cell.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)