Comments on: How to use SUMIF function in Excel with formula examples

If you are faced with a task that requires conditional sum in Excel, the SUMIF function is what you need. It is a really great function that can help you make sense of an incomprehensible set of diverse data. Instead of adding up all numbers in a range, it lets you sum only those values that meet your criteria. Continue reading →

Comments page 7. Total comments: 346

  1. I want to do a monthly ytd total on a spreadsheet with the months in columns. I have a ytd column and am trying to put in the sumif forumla taking the months Jan - Dec and giving them a value of 1 - 12. I then, in the cell immediately above my formula, have the current month's value. My sumif function is =Sumif(h5:s5,"<=v8",h9:s9) my calculation would be then that it should add all of the columns numbers 1 - 11 as that is the number displaying in v8 and not add the number in the 12 column. However it is adding it. What am I missing?

  2. Hi,

    Thanks for this wonderful step-by-step teaching about Excel. I find it very useful.

    I have a problem regarding SUMIF issue. Let me explain.

    =SUMIF(Inc!$M$1:$M$1000, ">=1/2/16", Inc!$J$1:$J$1000)- SUMIF(Inc!$M$1:$M955, ">=7/2/16", Inc!$J$1:$J$1000)

    How can I change ">=1/2/16" and ">=7/2/16" to a cell number like B2 or C3 that contains the particular date like 1/2/16. So, I have made 2 cells that have 1/2/16, and the other one is 7/2/16. Instead of having to input the date manually in the formula, I want to input the cell number that already has the date information in it so I can just copy it down.

    Thanks

    Iwan

  3. please how can i have continous additions of numbers each time new numbers is imputed in excel

  4. I have a column of finishes, I only want to count the total finishes below 41.

  5. Hi how can i calculate sum of range if the range has to be given by user.

    If i give 5 in a cell and i need sum of range from a1 to a5 in my result cell B2

    And if i give 7 result should be sum of a1 to a7

  6. AND IS THERE ANY OPTION PROCESS ETC THROUGH EXCEL CAN FLASH OR REMIND 15-30 MINUTES BEFORE ANY MEETING ETC

  7. IN A FROM 1-31 IS DATE N IN B C D ... IS THE NAME OF THE PERSON
    IF B IS PRESENT IN DAY 1 I HAVE TYPE Y AND IF NOT THEN I HAVE TYPE N. NOW I WANTS TO SUM THE HOW MANY DAYS B OR C OR D WAS ABSENT OR PRESENT HOW CAN I GET THE RESULT IN LAST CELL/COLUMN NO IN 32 NO CELL

  8. You are Great!
    I have solved a problem that took a long time.
    Thank you so much

  9. Please, how can i find two cells having same numbers in excel. For example if A1 and B1 with numbers like A1=34 and B1=39. how can i find cells side by side with same value as 34,39. e.g H15=34,I15=39.

    Thanks,
    Jolly.O.A

  10. I have 2 worksheets (1 is Contributions given - the other a Category Balance sheet). On the Contributions sheet I have Date, Name, Amount, Tithes, General Fund, Sunday School, etc. I will have multiple entries for each Date & I want to take the totals by Date & put on the Category Balance sheet. How can I create a formula that will look in the date column & total up each category based upon the date & autopopulate the results into the Category Balance sheet for each category?

  11. How do you add cells in columnB. If B1 to B365 is equal to 1. If there is zero do not add and start again to the first row that is equal to 1. I need to know if there is straight 30, 60 and 90 days. Column A is the date field and Column B is the number of days without late, thus, contains either 1 (without late) or 0 (with late).
    Ho many 30 days in a year. How many 60 days and how many 90 days without late?
    I will appreciate any help.

  12. Hi I am struggling with a cashflow and am hoping someone can help.

    I have a cashflow laid out like this:

    October 2016 November 2016 December 2016 January 2017 February 2017
    £5000 £5000 £5000 £5000 £5000

    I am trying to get a formula which automatically adjusts and counts the total of the cashflows from todays date to the end of my lease. Ie if todays date is November than count cash flows from November until end date.

    Thanks

  14. Hi,

    I have a table.

    Name Subjects Marks
    Amy Maths 58
    Kishan Maths 78
    Leon Maths 68
    Ankit Maths 65
    Rey Maths 53
    Amy Science 57
    Kishan Science 65
    Leon Science 92
    Ankit Science 39
    Rey Science 98
    Amy Social Science 69
    Kishan Social Science 98
    Leon Social Science 56
    Ankit Social Science 87
    Rey Social Science 96
    Amy English 78
    Kishan English 68
    Leon English 54
    Ankit English 63
    Rey English 97

    I have to copy same data in other table suing countifs. Can you please help me?

    SL# Maths Science Social Science English
    Amy
    Kishan
    Leon
    Ankit
    Rey
    Average

  15. how can I

  16. my teacher told me to explain these formulars = sum(A2:F2)

  17. Morning,

    Probably simple but cannot get excel to do the following,

    I want a cell to auto count when a single date is entered multiple times in a separate tab,

    Ex - if 1/09/16 is entered 4 times then the cell in the other tab will count 4
    if 2/09/2016 is entered 5 times then the cell in the other tab will count 5 and then if entered more times or deleted will auto adjust itself.

  18. I want to sum a range of numbers based on a specific date -30 days. I am trying to sum up the hours flown in the past 30 days, 30 days, 90 days.

    ex: 9/20/2016-30 days
    9/20/2016-60 days

  19. Hello need help for the following:
    i have 2 collums, in collum A i have part no in collum B i have quantity.
    Part numbers in collum A may be the same in more than one row.

    So what i want to do is that.

    find same part numbers in collum A, then sum their quantity from collum B and give me the total in collum c.
    Something like that:

    A B C
    200855 10
    200855 15
    25

    200856 25
    200856 15
    40

    Thank you

  20. I have table like below: how could I sum the cells that contain "S" in each column? help would be appreciated. in the below table the output should be .4 for column1 and .5 for column2

    .5E .5E
    .2U

    .2G
    .1S .2S
    .1L .1L
    .1U

    .3S .3S
    .7E .7E

  21. Thanks Svetlana. You have really helped a great deal.

    Please, how can i find two cells having same numbers in excel. For example if A1 and B1 with numbers like A1=34 and B1=39. how can i find cells side by side with same value as 34,39. e.g H15=34,I15=39.

    Thanks,
    Jolly.O.A

  22. how to use sumif formula for datewise total for particular party and party's total for particular date in one coloumn

  23. amt 3k if % less than 50%. to calculate total amt x 106%
    amt 3k if % more than 50%. to calculate total amt x 115%
    can advise on the formula?

  24. Can anyone help me I have this 5000 list 1) column1- pan / name of customer
    2)column various transaction types like P/T/S/R (as per the customer transaction details its short)
    3) consists of total amounts and one customer has many transaction.. I used Sumif formula to get their total but I want a specific customer name showing total amount where as only transaction of (P)..
    Please help

  25. i need sumifs total for each person

    A b c d e f
    john 19,018 2,408 1,000 2,000 16,610 145,341
    babu 4,563 578 1,000 2,000 3,985 15
    raju 62,865 7,961 1,000 2,000 54,904 346,323
    john 125,000 15,830 1,000 2,000 109,170 109,170
    babu 63,697 8,066 1,000 2,000 55,631 55,631
    raju 100,000 12,664 1,000 2,000 87,336 87,336
    raju 50,806 6,434 1,000 2,000 44,372 1,352
    raju 100,000 12,664 1,000 2,000 153 87,336
    raju 59,115 7,486 1,000 5,000 132 51,629
    babu 39,098 4,951 1,000 5,000 1,546 34,147
    babu 500,000 63,319 1,000 5,000 15,463 436,681
    babu 25,000 3,166 1,000 5,000 1,566 21,834
    john 181,934 23,040 1,000 13,200 19,761 6,301
    john 154,973 19,625 1,000 16,565 135,348 135,348
    john 25,898 3,280 1,000 45,646 14,564 22,618
    john 150,000 18,996 1,000 54,644 1,564 131,004
    babu 612,971 12,019 1,000 5,465 68,546 600,952
    babu 325,000 1,000 456,468 74,563 325,000

  26. =SUMIF(A2:A10,"",C2:C10) sums values in column C if the corresponding cells in column A are not blank, i.e. they have some content. Therefore it will include values where the cell in A column contains a formula even if its result is "".

    To work around this issue try =SUMIF(A2:A10,"><",C2:C10) instead.

  27. i am using this =SUMIF(A3:A33,"<="&TODAY(),K3:K33) excel formula to calculate MTD whhich will compute sum upto today as i enter data everyday.

    however need help to see how to calculate MTD average percentage !

    [Note: column A being Date 1 to 31 and
    column K being Occupancy%]

  28. Thanks.

    Please can one do nested If over 64 level of nexting?..

    Kindly advise.

    Thanks,
    Jolly.O.A

    1. Hi Jolly,

      To my best knowledge, there is no way to nest more than 64 levels of If in Excel. You can check out the following alternatives to nested IF's. For example, you can use the CONCATENATE function that allows supplying up to 255 arguments in modern Excel versions, which equates to testing 255 different conditions.

      BTW, did you get my email regarding the minus sign? In case it did not reach you for some reason, the problem was in your nested IF formula in cell B5. Because the numbers are enclosed in double quotes, Excel perceives them as text strings, and as the result other formulas referencing B5 yield wrong results. As soon as you remove the quotation marks, both formulas will work fine, and no minus sign will appear in B7.

  29. IF A3 > B2 , SUBTRACT B2 FROM A3, AND IF B2 > A3 ,SUBTRACT A3 FROM B2. WITHOUT GETTING A MINUS(-) SIGN. BOTH CELL A3 AND B2 CONTAINS FORMULAS.

    KINDLY ASSIST.

    1. Hi Jolly,

      Because you are always subtracting a smaller number from a larger number, there should be no minus sign in the result. It makes no difference whether the referenced cells are filled manually or using other formulas. It's difficult to say what is causing the minus sign to appear without seeing your worksheet. If you can send it to me (support@ablebits.com Att: Svetlana), I will try to help.

  30. THANKS FOR THE SWIFT RESPONSE.

    THE PROBLEM NOW IS THAT THE RESULT IS SHOWING A MINUS SIGN. MAYBE BECAUSE I ALREADY HAVE FORMULAS IN BOTH CELL A3 AND B2.HOW DO I CREATE THIS FORMULAR WITH GETTING A (-) SIGN. THANKS

  31. This is exactly what I was looking for, thank you!!

  32. help to make the formula to count total of following.
    1 2 3 4 5 total
    P P P H P ???

    if p=1,than total sum=??

  33. Hello,

    Please help me with the formula/function to be used in order that i can get the total sum of the credit limit of a group of customers as shown below:
    Customer Credit Orders
    Cust#1 100 17
    Cust#2 200 100
    Cust#2 200 50
    Cust#3 300 100
    Cust#3 300 150
    Cust#3 300 50
    -------------------------
    Total 600 467
    =========================

    Thanks a lot!

  34. I am working on an monthly evaluation form for employees. It needs to sum cells and calculate a percentage but only if data is entered in a cell. An example of the basic set-up of the sheet is:

    Professionalism: [Graded on a scale of 1 to 10]
    Positive Attitude
    Ability to relate to peers
    Ability to relate to supers
    Ability to relate to customers
    Communicate orally
    Communicate verbally
    Support other beliefs
    Respect differing opinions

    Every employee would be graded each month and the scores would be averaged to give a percentage, but only if data is entered.

    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
    5 6 7 60%

  35. Thanks for helping me about the use of IF operator.
    But I want to ask you how to use NOT operator within the AND and OR statement.

  36. I have a Summary sheet full of all of the products which are available. It contains a Total number of products. The products are Booked In on one sheet and are then Booked Out from another, the Summary sheet contains a SUMIF-SUMIF which calculates the total. I require a warning system or conditional formatting to indicate the Min/Max for a total. Not all products have the same Min/Max.

    The problem that I am having is that if I set up conditional formatting for specific cells and a new product is added, it moves the conditional formatting to another cell. In theory is it possible to make sure the conditional formatting sticks with its specific cell/product number? Or is there another method?

  37. Can someone PLEASE help me?

    I am trying to get a some of column A1:A1500 (only numerbic values) however, I have 2 additional conditions - I only want Excel to add the numerbic values in A1:A1500 for Nike's (type of sneakers) and the color Pink (color)

    F:F is where the different types of Sneakers are listed and and column H:H is the various colors. I am trying to create a formula that will add (total sum) of the #'s of sneakers bought per customer (0-17) which is in column A:A - Pink Nike's ONLY?

    Thanks in advance!

  38. Hello,

    I need to use a SUMIF formula I am pretty sure.

    I have one sheet with outstanding invoices on it, and another sheet with a total for each customer as they aid I mark them YES for paid.
    I need to work out what the formula can be so the paid invoices are not included in the total for outstanding.
    Can anyone help?

  39. Hi,

    I want to use special character as >, 1 5
    >1 10
    |K 3
    |K 1

    Summary
    Codes Amount
    >1 15
    |K 4
    I need to use sumifs because i had multiple conditions in my table

    Thank

  40. Hi my homework says "In cell B33, enter a formula to add the individual 5 cells one at a time." how do I go about doing that?

    1. Hi, I just happen to be on the site to see if there was an answer to my question.

      For you, the easiest way is just to click on B33, enter =SUM(B1:B5) into the formula space, click the check mark next to the fx icon.

      If you did it correctly then when you enter the values in cells B1 through B5 then B33 will add those independent values.

  41. First Example:
    A1 (Date); B1 (Bucket Size of 2.5 gallons); C1(Seconds To Fill Bucket); D1 (Gallons Per Minute).

    Quest:
    A1 (Date); B1 (Seconds To Fill Bucket);C1
    Gallons Per Minute

    Hi,
    I’m cyber (computer) un-savvy . . .
    I succeeded with three cells, in that, that equation =SUM(B1/C1*60) imposed in D1 will provide me with a correct answer. . Note, A1 is just the date of occurrence and is NOT necessary for the equation.
    My quest is to formulate the equation so that it only involves two cells. B1 represents the constant (2.5 gallon bucket) that is divided by a variable (time of seconds to fill the bucket) that is entered into the cell, and C1 is the hiddeb equation that displays the answer (gpm). In essence, the equation is 2.5/B1*60=gpm . . . 2.5 is a constant bucket size, B1 is the variable of time (seconds to fill the bucket), *60 is a constant (60 seconds) = ANSWER (gallons per minute)
    I have partial success, in that, C1’s equation =SUM(2.5/A1*60) functions if A1’s value is less or greater than 0 (zero). However, with the aforementioned equation, if the B1 value is 0 (zero) or left empty (implication that a sample was not obtained for that date) then there is an annoying and always present cell admonition of #DIV/0!.
    Can you help???
    Blessings,
    Tim

  42. I have never commented on such a forum before. However, thought you deserved truck loads of positive feedback on a concise, easy to understand, educational post. Thank you.

  43. I wish to set up a criteria in such a way that it adds numbers in a range but the criteria excludes certains conditions within the criteria range.

    Row A B
    1 212200 10
    2 218000 20
    3 214000 40
    4 215000 50
    5 216000 60
    6 217000 80

    I this example I want to add up column b(b1:b6), criteria range (A1:A6), criteria is anything => 212200, but <=217000 and exclude 215000 to 216000.

    Please help...

  44. Hello, I cam trying to create a formula where I have 2 cells - one is the gross weight and the other is the dimensional weight. sometimes it differs on which one is greater. I need to create a sum of multiplying the charge by which ever the weights is greater. Can you please help me?

  45. I am trying to work on a sheet where i have customer sales details of two products from which i want to calculate which customer has purchased what product in total. for one i cn calculate using "sumif" formula but its difficult to change the for formula for each customer.. can you please suggest me

  46. hi
    i have row with the following data in the cells:
    A3=20, B3=50, C3=PAID, D3=60, E3=PAID, F3=60, G3=PAID, H3=20, I3=40, J3=60
    I WANT TO SUM THE FOLLOWING CELLS: A3, C3, E3, F3, J3
    AS A RESULT I GET AN ERROR NAMED "VALUE".
    HOW CAN I SUM THESE CELLS WITHOUT GETTING AN ERROR.
    THANK YOU,
    ZVI

  47. Hi
    I am needing a formula that sums only if both cells have a value.
    And that answer will be multiplied by another cell
    For example =a25+b25*g28
    The formula is put into c28, I don't want anything appearing in c28 until both a25 and b25 contain a number or value, It needs to wait to add until I put a number into both cells,
    Thank you soo much for your time,
    I am useing 2013 excel

  48. Hi

    I'm trying to set up a prepayments schedule and I have the start date, end date, amount and daily rate shown is separate cells for an invoice. I have 12 further columns for each month of the year. I would like to have the invoice amount split between the months according to the number of days relevant to the month. eg an invoice may run from 13th of 1 month to the 12 of another. is there a formula that will calculate the number of days relevant for the month and multiply this by the daily rate?

  49. I am trying to create a formula that puts in a cell an invoice amount based on different date ranges.
    The invoice amount will be $1000 if the invoice date is between 2 date ranges in a month say August (100 %)
    The invoice amount will then drop to 900 if between 2 date ranges in September(90%)
    The invoice amount drops to say $800 if between 2 date ranges in October.

    1. Hello Michael,

      You can use the following kind of formula for your task:
      =IF(AND(A1>DATE(2015,8,1), A1<DATE(2015,8,31)),1000,IF(AND(A1>DATE(2015,9,1), A1<DATE(2015,9,30)),900,IF(AND(A1>DATE(2015,10,1), A1<DATE(2015,10,31)),800, 1000)))

      It allows you to list several conditions for different values that you want to show. Please also see for examples of using dates in your formulas.

  50. I don't think this site is being monitored anymore. I don't see any answers.

    Hello?

    1. Hello Jeannie,

      We apologize for the delay, it takes time to look at all the comments we get and provide a solution. Please find the formula for your task above.

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