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

  1. My data is Like this

    Column1 Column2
    Desired 100
    Actual 100
    Desired 75
    Actual 75

    I want to count all the Column data with Desired value of 100 and 75
    Means at the end Result will come 2

  2. I have employees' calendar: names from top to bottom, dates from left to right. Every Saturday column is highlighted. I have added a column next to names, so I am looking for a formula to add +1 to a specific cell if any cross cell (Saturday-name) contains text. This is to count on how many days off a person has worked. Thanks for the help

    1. Hi! Use the FILTER function to get all records for a specific employee. The ISTEXT function will check in which cells the text is written. Then we will use multiplication to get 1 in the cells where the column heading is "Saturday". The SUM function will help us find the number of such cells. For example:

      =IFERROR(SUM(ISTEXT(FILTER(C1:Z10,A1:A10="John"))*(C1:Z1="Saturday")),0)

      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

      1. Thank you for your fast reply. Trying to find out how it works.
        Hope this will explain what I am looking for
        But looks like you gave me links that can help me after I study the info there.

        Thank you once again!

        My sheet looks like this:

        _____ M_T_W_Th_F_St_Sn_M_T_W_Th_F_St_Sn_M_T_W_Th_F_St_Sn
        extra cell John o_o_o_o_o_o_______0_0_0
        extra cell Jane o_o_o_________o_o_o_o___o_o_____o_O_O

        So as formula's result in Jane's extra cell I am looking for a 2, since there're 2 x cells consisting text at Jane and St crossing.

        1. According to your data, the formula might look like this:

          =IFERROR(SUM(ISTEXT(FILTER(C1:Z10,B1:B10="Jane"))*(C1:Z1="St")),0)

  3. Hello,

    I would really appreciate your help for a formula that will help me count the values from a column that looks like below. My problem is that I have cells, where are multiple values, delimitated by space.
    5
    3 4
    2
    2 1
    1

    Thank you for all your help.

    1. Hi! To find the count of numbers in a range when more than one number can be written in a cell, try the formula:

      =COUNT(--TEXTSPLIT(TEXTJOIN(" ",TRUE,A2:A5)," ",,FALSE,,0))

      To find the sum of such numbers, use the SUM function instead of COUNT:

      =SUM(--TEXTSPLIT(TEXTJOIN(" ",TRUE,A2:A5)," ",,FALSE,,0))

      Merge all numbers into one text string using the TEXTJOIN function. Then split the text into individual numbers using the TEXTSPLIT function.

  4. I have a spreadsheet with multiple tabs. I want to do the following:

    Spreadsheet tabs #1 is Detail. This lists the individual prices of each item assigned to a client. Spreadsheet tabs #2 Case and is the summary for each case. I want to take the detailed prices for each client and bring that total of all the detail to the second sheet and have a grand total.

    If tab "Detail" has a "Case #" = 44521, then take all the "Extended" prices on that tab and summarize them to the tab "Case" for that specific client

    1. Hi! You can find the sum of all prices for "Case #" using the SUMIF function. But I'm not sure it makes sense to sum the prices. If you want to get all the prices for a specific "Case #", use this guide: Vlookup multiple matches in Excel with one or more criteria. You can also use the FILTER function to get a list of prices. Look for the example formulas here: Excel FILTER function - dynamic filtering with formulas.
      It is my hope that my advice will be of help to you in your task.

  5. I want a formula where the cells in a specific range will be counted based on data and criteria in another range.
    in the other hand how many cell is in A1:A10 if there is Bi=10,i=1 to 1o

  6. Thanl you for the response. That equation doesn't take in to account the other sheet that has all the data to pull from. What is the best way for me to send you the data?

  7. I have two sets of data side by side, each with a country and a value. If the country differs (ie text A is different to text B), I want the value of the second country.

    So essentially what is the formula within sumif to say 'if text A does not equal text B, then ?
    Help!

    1. Hi! If I understand your task correctly, the following tutorial should help: Compare two columns for matches and differences. If you want to compare the values in the corresponding rows, try the IF formula:

      =IF(A1=D1,B1,E1)

      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail. Give an example of the source data and the expected result.

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

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

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

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

          1. That works perfectly, thank you so much!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    1. Hi!
      Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail?

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

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

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

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

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

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

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

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

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

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

  31. What formula would I use (or is there one) to total the hours employees took to take certain staff training requirements (example table below):

    A B C D E F G
    1 Employee Name Course 1 Course 2 Course 3 Course 4 Course 5 Hours Completed
    2 Course Length 3.25 1.25 1.50 1.00 0.75
    3 Employee 1 10/17/22 10/12/22 10/12/22
    4 Employee 2 10/16/22 10/04/22 10/12/22
    5 Employee 3 10/17/22 10/13/22 10/13/22
    6 Employee 4 10/17/22 10/17/22 10/11/22 10/11/22

      1. Sorry the table didn't come across very well, but that's exactly the formula I needed. Thank you!

  32. Is possible to sum this text?

    Some text in cell. r=11

    R=1,5. Some text in cell.

    Sum all r=? and, that's in one row.

  33. Hello.

      1. Thank you very much

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

  35. Hi,

    I need help to assign weights to text. For example, in the following response table,

    Question No Response Weights
    Q1 Red 1
    Q2 Blue 2
    Q3 Green 1
    Q4 Red 2

    Clearly, questions 2 and 4 are having higher importance. When commuting the response, it needs to read as
    Red: (Q1*1+Q4*2) = 3
    Blue: (Q2*2) =2
    Green: (Q3*1) =1
    Total =7 (not 4)

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

  37. Hi and thank you.

    Stuck trying to work out formula for staff roster, whereby in a row of cells x 7 (one week) each shift worked (x 3 shifts, which equal 12 hours each) adds the sum of these shifts to a separate cell.
    Eg.. Staff Name / Sum of Total Hrs / LD / / LD / LD
    Should look like-
    Staff name / 36 / LD / / LD / LD

    This will actually be over 6- week roster, but just trying to get an idea of what to do in the above eg.
    Thanks

    1. Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =COUNTIF(C1:I1,"LD")*12

      Use the COUNTIF function to count the number of specific values in seven cells.

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

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

        1. I have to calculate a total cost per date range for each event. I have a sheet that has the daily rate for different pay scales. I am trying to get the total cost for example: Cell B8 has a drop down for different pay grades. Cell E8 has the start date. Cell F8 has the end date. Sheet 2 has the data to pull from. Sheet 2 column B has the pay grades. Column C has the daily pay rate. How do I get on Sheet 1 Cell H8 to calculate that?

          1. Hi! I need to see your data to give you an exact formula. Perhaps this formula will help:

            =(F8-E8)*B8*C8

            Use the company's payroll manual.

            1. How will it know what pay rate to use? I in sheet 2? What is the best way for me to send you the data

              1. Thank you for this. I am still trying to learn xlookup. This was very helpful. I have been able to get most of what I need. Thank you again for all the help

              2. Thank you that worked. Now how do I in the same formula tell it if there is an additional function to look up. So far I have: =xlookup(B$8:B$900,SHEET2!B2:B29,SHEET2!C2:C29*G8,FALSE). If this is their pay without a daily stipen how do I tell it to add the daily stipen to this formula? Sheet 1 Column D will have a dropdown yes or no for the stipen. sheet 2 will have the stipen for the correct pay grade. Sheet 2 column D will have a drop-down for yes or no. Sheet 2 column E has the pay grade and column F will have the daily stipen rate. Example An O3 has a stipen of $33.00 But an E3 will have a stipen of $8.00.

  40. 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. Hi!
            It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

          2. Because this is not working in single cell.

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

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

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

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

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

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

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

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

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

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

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