Comments on: Excel SUM formula to total a column, rows or only visible cells

The tutorial shows the most effective ways to use Excel's AutoSum feature and write your own Sum formulas to total a column, rows or selected range. You will also learn how to sum only visible cells, calculate running total and find out why your Sum formula is not working. Continue reading →

Comments page 2. Total comments: 81

  1. Please help me,To find the automatically sum amount of particular raw if i colored raw.for example i prepared chart of receivable amount.when i received money i just shide the color on who gave money.i want to know how to make a formula to avoid the colored raw.please help me

  2. define for me the following terms used in spreadsheet
    Function
    An operator

  3. am appreciative for all your services

  4. D/Sir
    To find the Total I am using the formula subtotal(109,M10:U10) but the result is adding the hidden columns also.Any solution to this problem?
    Thanks
    Sudip C. Pal

  5. Question 1: Can someone help me understand how this formula works
    A1:1
    A2:2
    A2:3
    =Sum(A1:A3-1)= 2 ## Need help with this Question?
    Question 2:
    A1 B1 C1
    =Sum(A1:C1)-Sum(A1-1:C1-1) ## this Formula is not working, any comments?

    1. answer to Question 1:
      SUM(A1:A3-1) subtracts 1 from the value of each cell within specified range (A1 to A3), then adds the resultants.
      (1-1)+(2-1)+(3-1)=0+1+2=3
      answer to Question 2:
      SUM(A1-1:C1-1) generates an error.

  6. In a file, i maintain 2 sheet summary and detail. Detail sheet is datewise and summary sheet is tank no wise, so i did filter in detail sheet and in summary sheet i used formula of subtotal(9,(range)) for calculating total amount against particular tanker. But when i remove filter, total of summary sheet gets changed. So please let me know which formula I have to used.
    Thanks.

  7. today i enter the value in column B5 and the total of column B5=c55, next day i enter the value in column b5, then it must added automatically in column C5 automatically

  8. Thanks. It helped me a lot.

  9. the auto sum for visible data (not hidden) only instructions do not work

  10. I have to find the cgpa of my class result...i have done summing up amd grading in excel sheet...but cant find out the formula

  11. Too much thanks, It's very useful.

  12. Hi,
    How do I sum visable cells only(filtered)
    VISIBLE CELLS POPULATE A ROW

    COLUMN (FILTERED)
    C G H K L N

    ROW...... 6 3 5 9 2 4 = SUM ?

    Thank you

  13. I have a problem to sum only the digits which cells are formatted by kg, like

    5 kgs
    4 yds
    3 kgs
    2 kgs
    7 yds
    _______________

    I would like get sum result only with the kgs, above result should be 10 kgs.
    Could you pls advise how to solve it.

    1. It can be done pretty simply if you split the text from the numbers like this:
      Where the numbers are in A29:A33 and the text is in B29:B33 the formula is:
      =SUMIF(B29:B33,"*kgs*",A29:A33)

  14. How to find the sum of formula cells only ?

  15. My problem is viewing:
    If I enter amounts with decimals or cents I am not able to view the whole total (the amounts to the right of the decimal point). How do I extend the area to view the entire total?

  16. I am trying to sum columns and if the total is 40or more, change the total to 40 which is the set up maximum. And if the total is less than 40, then just show the correct total. Can somebody help me with the formula? Thank you.

    1. Hello, Rey,

      If I understand your task correctly, please try the following formula:

      =IF(SUM(A:A)>40,40,SUM(A:A))

      Hope this will help you!

  17. I would appreciate if someone could help. I have an excel document that is exported from simply. It has a column with subtotals (=SUBTOTAL(9,H12:H17))for each property and I would like to total the subtotals without having to do in manually.(c5+c17+c27) Thank you in advance

  18. Hello everyone,Challanging question for me (Please understand the issue carefully before answering hapazardly)
    C1:C10 (weight gain)= 1,2,3,4,5,6,7,8,9,10
    A1:A10 Age of child in month) = 11,20,23,40,55,60,32,80,90,95,160
    Now I make class interval in B1:B6 as (0-1)(2-6)(7-12)(13-24)(25-60)(61-168) for A1:A10
    I did: sum age of child meeting (0-1) in B1 which is always showing 0 gram weight increase, for those child who doesnot exist (mistake calculation), want to display empty cell for such case and sum only of there is value in column B. Formulas working fine for the existing child.
    I couldnot localize how to calculate for those rows that donot exist in A and display empty cells in B.
    Again, for reminder: excel is showing 0 gram increase for those child that doesnot exist in my excel sheet.
    I tried 100s of formulas but non of them worked.
    Please suggest same formula for all calculating in all class intervals as mentioned above

  19. Hello everyone,Challanging question for you all (Please understand the issue carefully before answering hapazardly)
    C1:C10 (weight gain)= 1,2,3,4,5,6,7,8,9,10
    A1:A10 Age of child in month) = 11,20,23,40,55,60,32,80,90,95,160
    Now I make class interval in B1:B10 as (0-1)(2-6)(7-12)(13-24)(25-60)(61-168) for A1:A10
    I did: sum age of child meeting (0-1) in B1 which is always showing 0 gram weight increase, for those child who doesnot exist (mistake calculation), want to display empty cell for such case and sum only of there is value in column J. Formulas working fine for the existing child.

    I couldnot localize how to calculate for those rows that donot exist in A and display empty cells in B.
    Again, for reminder: excel is showing 0 gram increase for those child that doesnot exist in my excel sheet.
    I tried 100s of formulas but non of them worked.
    Please suggest same formula for all calculating in all class intervals as mentioned above

  20. Please solved this example : -

    Analyze Data Actual Default
    A 486 53
    B 150
    C 10
    Total

  21. how to calculate total without sum function or + operator

  22. how to compute (G7x5)+(H7x4)in a horizontal row

  23. how to calculate the sum of the following?
    (G7X5)+(H7X4)+(I7X3)+(J7X2)+(K7X1)
    Please enlighten me..Thanks in advance...

  24. Ram 2000
    Raj 400
    Pop 221
    Arun 339
    sohan 3382

    I want to calculate the total of all.. but skip "pop" and "ram" automatically....
    Eg:
    Ram 2000
    Raj 400
    Pop 221
    Arun 339
    sohan 3382

    the total of this will be "6342." but it automatically skip pop & ram & give answer "7721."

  25. it is very well...i cleared all my doubts...but how i can iget ablebits in my windows 10 computer

  26. I want to total all all rows in Column A minus Column B row1,2,3... sum into Column C row 1,2,3... need a formula please

  27. I want to total all all rows in Column A minus Column row1,2,3... sum into Column C row 1,2,3... need a formula please

  28. i have small doubt in excel pl solve this. details given below
    i have on table some rows and columns
    sl.no. amend no. scope qty amt
    1 1 1234 1500 1000.00
    2 2 1234 50 800.00
    3 1 324 100 200.00
    4 1 545 10 50.00
    6 2 324 87 65.00
    7 3 324 54 85.00

    Now i want like every scope highest amend no details.
    pl help me

  29. I have a budget worksheet by month. Totaled income/expense at the end of each month. Jan=column B etc....but when adding income/expense to Feb (column C) is adds or remove from my January total.

    My formula for January is =SUM(B62-B49)so why is adding to column C effecting this?

    Thank you

  30. I want to run an inventory sheet where I can take from a beginning number or add to that same number and have it give me a quantity on hand. Example. I have 20 of something and tomorrow I remove 4 of them so I need a box to calculate that I now have 16 but the next day I add 10 back into the qty- I now want that box to show I have 26.

  31. I want to know i have excel sheet i want to fix the SUM formula after filter the sheet in some selected row actually i have tried after filter but which cell is hide that cell Qty also adding in total please check tell me if you have any formula.

    1. =Subtotal(9,c2:c6)

      inplace of c2:c6 substitute the column and row details as per your worksheet

      1. hello, I have another doubt in which i was able to add filted numbers but when i open the unfiltered sheet it goes back to sum of all the numbers. please help.
        i filtered the items to make a total of different items but when i change the filter to "all" it changes to sum of all the numbers in the range.

  32. lots of good stuff herein - thanks!

    But, I want to make bold the "sum" product of auto-summing in the bottom bar of the Excel screen (where "sum" is accompanied by "average" and "count").

    Can this be accomplished?

    I'm 85 and even with glasses the sum product is not very visible. Microsoft should change this and make it bold. All octogenarians would give a shout-out if they did.

    Tom

  33. Dear Sir,

    actually i have approximately 25 row in that i want to sum like this way
    1row +3rd row + 5th row + 7th row ...... if there is any formula please provide me

  34. How to sum a whole row

    To sum the entire row with an indefinite number of columns, supply a whole-row reference to your Excel Sum formula, e.g.:

    =SUM(2:2)

    Please remember that you shouldn't enter that 'Sum of a row' formula in any cell of the same row to avoid creating a circular reference because this would result in a wrong calculation, if any:
    mam please elaborate this with xls file i am not able to understand it

  35. How add if table like 24kg
    25 kg

  36. What is the formula to sum up a total billing per Customer# by Customer name. I also need a formula of average billing and the highest billing per Customer.

    Example:
    Column A B C D E F
    Customer# Customer Name Bill Amt Total Pd Amt Avg Bill HighestBill YTD
    1 A $125 $125
    1 A $0 $0
    2 B $85 $85
    2 B $225 $225
    3 C $35 $35
    3 C $67 $67
    4 D $324 $324
    4 D $455 $455
    4 D $124 $124
    4 D $0 $0

    I need the SUMIF formula if applicable. Thank you for your help.

    This formula is good but can I replace row number for customer name? =IF(AND($B2<"", $C2"", $D2""), SUM($B2:$D2), "Value missing")

    Tina

  37. What is the formula to sum up a total billing per Customer# by Customer name. I also need a formula of average billing and the highest billing per Customer.

    Example:
    Column A B C D E F
    Customer# Customer Name Bill Amt Total Pd Amt Avg Bill Total Amt YTD
    1 A $125 $125
    1 A $0 $0
    2 B $85 $85
    2 B $225 $225
    3 C $35 $35
    3 C $67 $67
    4 D $324 $324
    4 D $455 $455
    4 D $124 $124
    4 D $0 $0

    I need the SUMIF formula if applicable. Thank you for your help.

    Tina

  38. Helpful!!

  39. ms office is the heart of computer programing

  40. I'm a new user I have a units and and unit cost say I have 50 units and each are 1.99 how do I get home he sum.I would really appreciate it.

    1. Setting up a work sheet I want to use only 4 columns: A=Date B=Invoice # (ie 2012000) C= amount ie (345.00) D=running total of column C of each entry as applied or entered
      Also is a column header needed for each column?

  41. I have a data table that is updated daily, for example, a new column is added daily, I want to find the difference between the last updated column & first column. But, don’t want to rewrite formula each time.
    Can you help me...

  42. thanks for the tip on How to sum only filtered!!!

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