Comments on: How to subtract in Excel: formula for numbers, percentages, dates and times

Subtraction is one of the four basic arithmetic operations, and every primary school pupil knows that to subtract one number from another you use the minus sign. This good old method also works for subtracting numbers and cells in Excel. Continue reading →

Comments page 2. Total comments: 109

  1. I had an old timer show me this formula many years ago, and now I'm the old timer who needs to use it again, but can't figure it out. I need to subtract Col. C from Col. D, and if the answer is a positive number it needs to go in Col. F, but if it is a negative number it needs to go in Col. E. This is for a surveying solution where the numbers in Col. C and D will always be different and the greater and lesser values of those columns can be in either of those columns. I hope that is clear, thank you very much.

  2. =((B16/B24)*(1-B24)*12) I want to achieve

    (B16/ B24) and (1-B24 ) multiply brackets and result to be multiplied by 12

  3. Hello, I’m trying to see if anyone knows if there is a way to subtract 2 values automatically every month. For example if I have 50 in a cell and I want to subtract 2 from 50 once a month how would I go about doing that?
    Thanks

  4. Hi,

    I need formula for subtracting number from last cell, If my Column A have 20 which is total days and Column B have 18 Days Column C have 10 Days Column D have 5 Days, I need to get result as Column D should be 0, Column C should be 0, Column B should be 13. Based on total Days needs to reduce Total A column from last Column D, then C and then B.

    Kindly help if anyone knows.

  5. How to do this in excel
    -3000 -(3000)

  6. Hi ,

    Please assist me on this ..

    1 error = -5 points from the total points.. how to do in Excel.. we require 2 error = -10 like that..

    Thank you

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

      =B1-C1*5

      B1 - total points, C1 - number of errors

  7. The value in A1 is 7.72%; the value in J1 is 12.79% (which is derived from adding C1+E1+F1). In L1, I want to get the value of J1 (12.79%) less/minus A1 (7.72%). What should be the formula in L1?

      1. Can you help me...I am doing a running balance for accounting for probate court.
        Say I started with balance of 1000.
        If column A is a withdrawals of 50. and
        Column B is a deposit of 1000., how do I get Column C to have the balance?

  8. if c1 value is 102+405 and d1 value is 104+605 and in c2 is 105+400 and d2 is 103+735 and so on, So how to subtract formula in column E

  9. I’m trying to subtract multiple cells (expenses) across the board- from a single cell in my total income cell. This seems impossible, because I have tried every formula I’ve found on 6 different websites on how to do this, each one is completely different- and this entire page is contradictory.

      1. This is what i am trying to do as well - If i have a total of $300 in cell A1 and i put in $10 in cell B1 what is the formula i would use so that A1 would now be $290. This is for a budget - if i'm starting with a total dollar amount available i want to track each time i pay out from that total until it hits $0.

        1. Hi!
          An Excel formula can change the value only in the cell that it is pasted in. If you need to change the content on cell A1 that has some value in it, you’ll need to use a VBA macro. You can learn more about running total in Excel in this article on our blog.

  10. Please can someone assist if I want to subtract 2.5% from an amount

  11. 00-24 - 04-07 how to subtract this kind of timing in excel

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.
      Thank you!

  12. You are using terms I don't understand. I just need to know:

    the formula to subtract row 23 from row 25 across ALL columns?

    1. Hello!
      To subtract each position of line 23 from line 25 use the formula

      =A25-A23

      Copy the formula to the right line by line.
      If this is not what you wanted, please describe your problem in more detail.

  13. Hi Svetlana,
    Is it possible to subtract the number of highlighted cells from a value in another cell. In context - I want to have a formula for amount of leave days available in one cell and deduct the number of highlighted cells (that correspond to days leave taken) from this ie days available = 10 - (3 highlighted cells in January) = 7 days leave remaining. Essentially not actually deducting a value but deducting a number of cells with no values within them.
    I hope this makes sense! Or maybe there is another way to do it?
    Many Thanks
    Becky

  14. Hello

    I have typed in exel sheet 1000 but shwing 10 why Please resolve

  15. Thank you soooo much, you saved me a lot of work.

  16. hey
    i want to know if it is possible to Subtract same name columns like for example if i have 5 apples and every time i type apple, one apple Subtract from these main number and these main number is in separate column.
    thanks

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

  17. Hi, Is there a way I can have a cell calcuate and keep a running total each week.
    Examaple
    I have 2 columns:
    C4-(Units to be billed)A number will be entered in this column weekly and D4 (Units Used) to keep a running total of data entered in C4 each week.

    So If I enter 4 in C4 .. it will calcuate 4 in D4 but the following week if I enter 6 in C4 it should populate in 10 in D4.

    Any help would be greatly appreciated.

  18. Hi I want to if it's possible that.. If a enter a value in a cell can it automatically subtract the value of a different cell.. E g
    Cell A1 has 940.. I want to manually enter 10940 in cell A2 and then it automatically shows 10000 in a cellA2.. Is there a formula for this

    1. Hello Andy!
      Either a formula or a value can be written in an Excel cell. If you entered 10940, then you can change this cell either manually or using the VBA.

  19. Hi there,
    I have a bit of a special request. I am trying to work out if it's possible to create a formula with numbers inside the same cell.
    A1: Order # (as a prefix) 0000 - 0000 (range of the orders number)
    B1: Combine order value
    C1: How many unique orders were made( 1002 - 1001 = 1 order)
    Example:
    A1 B1 C1
    Order # 1001 - 1002 Tot $ of the combined orders Numbers of unique orders

    If A1 was divided in 2 cells it would be easy to do. In my case I was hoping to keep the orders numbers in the same cell.
    Any idea if there is a formula so that I can extract the numbers after the "-" and subtract them with the numbers before the "-".
    Thank you in advance.

  20. Hi Svetlana

    I hope this find you well
    I am trying to find a formula to subtract the first 6 digits from a large string of digits, from one cell to another across column A and B
    column A Column B
    1234567891011122 123456
    1234567891011122
    1234567891011122
    1234567891011122
    many thanks
    Amanda

    1. Hello Amanda!
      If the result should be a number, then use the formula

      =--LEFT(TEXT(A1,"####"),6)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  21. Greetings....
    Is it possible to subtract a number in a cell that is created by a formula? Example: Cell D43 has manually inputted number of 53.99, Cell D44 has a number of -4.00 which is the product of this formula =IF(D43=45.99;"-3.24";IF(D43=47.99;"-3.24";IF(D43=53.99;"-4.00";IF(D43=63.99;"-3.25"))))

  22. Hello
    lets say i have column with salaries that sums up at the end of the column is it possible to subtract a cell by highlighting it with color >>> so when the cell is highlighted the the number is subtracted from the total
    thanks

  23. Hello,
    I need to create a formula to calculate shift times, column A is start time, column B is finish time but it goes over to the next day and excel does not recognise this when substracting =B1-A1.
    What can I do?
    Thank you

  24. Hello,
    I am attempting to create a formula that gives a value based of a range of cells when a new cell has data entered into. For example, i have set value in B1. When data is entered in C1, then i need the formula to =C1-B1. When data is entered in D1, then the formula should generate a value from D1-B1. And so on. Is this possible?

  25. Hello. Can you tell me the formula if i want to subtract 1gram for each 10gram. For example if i punch in 79 in a cell in another cell it will be 72. And if i punch in 50 it will be 45. Your help would be appreciated thx

  26. Lot of thanks team ablebits.com. This forum is fantastic. Your help is beyond words

    Best regards
    AK.

  27. good work!

  28. I’m trying to make a formula or command that if there is an actual number imputed in a specific cell then subtract the next actual imputed number from above in the same column. The numbers won’t be put in in the same cells all the time in that column. Example
    1 2 3 4
    A
    B 300
    C
    D 600. I want cell D2 to take D1 and subtract it from
    B1. But the formula needs to know that the 300 could have been in c1 or a1. I Want this to set up over 31 rows

  29. Thank you so much!!

  30. Hi. i'm mic. I have designed a box on a worksheet and includes letters From (A) to (Y) in columns B up to L out of 12 rows. The whole column A is where I input my data entry to receive results in this box. ie Column B has (5) edited bold letters from A to E. Skipping column C, column D has 5 letters from F to J. And skipping column E, column F has 5 letters from K to O. And this continues up to the last letter Y in column J. E.g Capital letter (A) is in column B2:3, letter (B) is in column B4:5, letter (C) is in column B6:7, and this continues up to (E) of column B. And this is also true to all the letters in the given columns. These letters represent the names of items that begin with these letters. And going to the cells of skipped columns, its where I receive the sum of each letter's existence in column A after an input. For example, letter (A) in column B has 2 cells, c2 and c3 in column C, and c3 (which is the down cell) is to give me the sum of A's appearance after its entry in column A. And here, all the down cells of the skipped columns are to give me the sum of each letters appearance in column A, and here I already used the COUNTIF function to do that for me. WHAT I WANT you to do for me is this. Lets say most letters in this box have different sums of entry from column A in their down cells, and in this case letter G has the sum of 6 entries in its down sum cell, yet G is the next entry but has not been entered in columm A since the last 13 entries. After its entry, how can I use letter G's upper cell (column C5) to count 0(zero) whenever G is entered and again start counting from 1 to 100( or more) if a another letter in entered. Or if letter A has the sum of 0(zero) in its down cell(that is column C3) since 36 last entries of other letters in column A, let column C2 (the upper cell) count 36 as its last(or none) entry in column A, and RETURN counting to zero if A is entered. Let the upper cells in the skipped columns of each letter independently count(sum) the number of times they were last entered and RETURN counting from zero when they have been entered in column A. What formula should I use to do this. PLIZ I NEED YOUR HELP. THX.

  31. ON HAND 1 2 3 4 5 6 7 8 9 10
    430 20 100 100 200 70 10 10 100 100 100
    COVERED COVERED COVERED COVERED ONLY 10 Not Covered Not Covered Not Covered Not Covered Not Covered

    the following line shows how the formula needs to put the correct info see below
    1 2 3 4 5 6 7 8 9 10
    0 0 0 0 60 10 10 100 100 100

    hi

    Based on my HAND I can cover from week 1 to week 4 and 10 pcs for week 5 and the rest of the week wont cover
    Can you please help me to get the correct funtion and formula to use on this task ? pLease help thanks

  32. Hello,
    I created a spreadsheet that will subtract my postal charges from the total amount I created in a purchase request. Example: The Purchase request amount is $3,000.00, which populates cell F5. the mailing charges are subtracted for each cell in column G, so in cell G5 I typed $5.22 and then the results are shown in column H, or in this case cell H5, which tells me my remaining balance is $2,994.78. I now want the remaining balances I see for each individual cell in column H to show up in column F so that I don't have to type it in each cell in column F each time I get the balance. For example I want the $2,994.78 I see in cell H5 to now show up in F6. Then I will have the $2,994.78 in F6 subtract the mailing charge in G6 and get the remaining balance in H6, which will then reflect in F7 and so on. Is there a way to make this work?

  33. PLEASE SUGGEST THE FORMULA FOR SUBTRACTION RESTRICTION WITH A NUMBER, SUPPOSE IN A TABLE VARIOUS ROWS FOR SUBTRACTION ARE THERE, THE AMOUNT TO BE SUBTRACTED IF BECOMES MORE THAN A CERTAIN NUMBER, THEN ALSO THE CERTAIN NUMBER ONLY SHOULD BE SUBTRACTED NOT THAT MORE NUMBER.

    1. How to perform subtraction of 3 billion (3,000,000,000) frm 2 Septillion (2,000,000,000,000,000,000,000,000)?
      It wun give the correct answer as if the 3B has been ignored.

  34. I need help could you teach me how can I take the bonus for 2142worked hours 1500 hours must subtract the remaining hour it cost 10$. can you show me what formula i should use to see the bonus? ASAP thanks

    1. TEXT ME HERE FOR CLASSES

  35. I need a formula in excel that will add and subtract accrued time but not allow total to go over 384?

  36. Dear Sir,
    I don't want to put in the value every time I want to subtract one particular number from that cell. I tried placing that number in one cell and click it with =a1-b1 but this when extended using the easy option it selects the next one so I don't get the value. I don't want to put it inside the formula every time, cause this will be entered by someone else, that value they put in should be subtracted from all without making them the use of the formula.

  37. THIS IS VERY IMPORTANT FOR ME
    THANK YOU

  38. i have followed this to the letter... just trying to subtract one column from the other.... does not work.

    1. Hi Dana,
      You may want to download our sample workbook and see how the formula is implemented there. Hope that will be helpful.

  39. I have a cell with the formula =DATEDIF(G7,C2,"d") to get the number of days since a breeding event for cattle. I would like to be able to have another cell that automatically subtracts that number from 282, changing each day (like a countdown of days left, where 282 is the total days in gestation so I know how many days until they are due).

    Thank you!
    Lindsay

    1. Hi Lindsay,
      How about using the Excel DAYS function instead of the DATEDIF one? If that is okay with you, the formula is as follows:
      =282 - DAYS(TODAY(), G7)

  40. Honestly, this is awesome! It was as if i was face-to-face in a classroom with my tutor. This is excel made simple. Thank you so much.

  41. I have value in cell a1 (1801381), I need to multiple that value by 1.05% (1891450), then subtract the value in cell A1 (1801381) from 1891450. What would that formula look like?

  42. subtract function does not exist in excel ???? but you write =c1-c2 let say. too me it looks like a function but it works only once why ?

    1. Hi Bob,

      Functions and formulas are a bit different things.

      Function is a predefined formula already available in Excel. Functions perform specific calculations based on the specified values, called arguments, or parameters.

      For example, Excel provides a built-in function to add up numbers: SUM(number1,[number2],...)

      A built-in SUBTRACT function does not exist. But you can make your own subtract formula like =A1-A2

  43. Hi,
    I'm currently trying to write a function for a time sheet at work and am having a little bit of trouble. My goal is to alert my boss when a task is close to being due but i've run into a bump thats making me scratch my head a little.
    C5= 193 (HOURS ALLOWED ON PROJECT) E5= 183 (HOURS ACCUMULATED)
    IF(C5=E5-10,"10 Hours Left")))
    Any help would be appreciated. Thanks

    1. Cameron,

      It's not really a formula but if you were to type the following, it'll give you results that I think you're looking for:

      =IF(E5=183,"10 Hours Left","")

      If you need more help on your spreadsheet let me know. Ann

  44. Hi..
    I have students list with Marks & Rank and i want to find 1-10 marks how many students and their ranks in Brackets
    For Example
    1-10 marks
    10 students(1,2,3,4,5,6,7,8,9,10)Ranks

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