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 6. Total comments: 346

  1. Hello. I'm trying to write a sumifs formula for multiple conditions in which the referenced criteria cells contain formulas and display the results. For example, instead of summing all the cells in a column where the value >=2, I want to sum all the cells where the value is >= a referenced cell that has a formula in it that equals 2. In older versions of Excel I did this with the Conditional Sum Wizard, but I can't seem to get it to work with Sumifs. Can you help.

    Thanks so much!

  2. Hi,

    How to sum cells which contains numbers and text either the numbers are in the beginning or at the end? I don't wanna remove the text/number or don't want to separate the text and the number.

    Please give me some idea.

  3. Hi, hope you can help me to find the best formula for my need.
    I would like to make revenue calculation of several customers.
    there is a commission that is changing between some of them - for example
    1. should be amount - 20%
    2. should be amount + 5%
    3. no extra commission

    I have used =sum with sumif for each one that needs extra commission change but than I don't know how to add in all the rest that don't need any modification without the sumif...my formula:
    =sum(SUMIF(B96,"customer1",E96)*$L$3)+(sumif(B96,"customer2",E96)*$L$4)+(sumif(B96,"customer3",E96))+(sumif(B96,"customer4",E96))-G96

    *note that I added customer 3 and 4 without any modification just so they are counted...but I need something generic for any other partner than 1 and 2
    any idea?

    Thanks!
    Dean

  4. Hi,

    I am working on a cash flow. I'm looking to create a formula which basically read's: If (A1) = (B1) then add (C1) to (D1).

    I hope this makes sense.

    Appreciate your help.

  5. Hello,
    I am working on an excel sheet for work and I need to create a cell that will add up how many rows I have that are a certain color and display that number of rows in the cell. I also have one that needs to add up the number of rows based on color AND a specific word in a cell within that row. If there a formula I can use to to this? I can only find ones that do one or the other.
    I appreciate your help!

    Ex: Some rows are green, some are yellow and others are orange. Green represents a kind of home and yellow a different and orange a third kind of home. The yellow and orange ones are only color specific and I have a cell at the top that needs to add up the number of yellow and orange rows I have and give a number. The green will change based on a column that will describe if this home is open to build or already being built. I need a formula that will only find the green lines with this specific text in the column and add those up and give a number value in the cell of how many green cells there are with this text. (I hope that helps)

  6. THANK YOU FOR SHARING US KEEP UP.

  7. Hello

    I have cells having letters or numbers and i want to add those cells if they have number and if the cell has letter should be discarded. How to do this?

  8. Hi there,

    I have a question on sumif and sumifs function. I have a list of data showing contracts names that took place over 2012-2014 for a specific client. I want to set up a summary tab where there are drop downs for contract name and years. so if I were to select 2014 and it'll give me a total figure of how much was made in that year. If I were to select specific contract name for 2014, it'll give me how much was made on that contract in 2014. Also if I was to select all years and all contracts, it'll give me an overall total.

    Would anyone happen to know how I would go about doing that please??

    thanks

  9. Hi,
    Is it possible to add a mathematical condition in Sumif formula. Please refer below example

    A
    1
    2
    3
    4
    5
    6
    7

    Can I add a condition in Sumif such that add only those numbers which are divisible by 3.

    1. One option is to insert a new column to calculate which values are divisible by 3 and use it as the criteria range for the SUMIFS formula.
      =MOD(cell,3) equal to zero means that the cell value is divisible by 3.
      Use the new column as the criteria range and 0 as the criteria in the SUMIFS formula to add the cells that are divisible by 3. Hope this helps!

  10. Hi,

    I am trying to autosum a total column in an invoice table spreadsheet in which it includes the data and the price visited per site. I have written a formula in the date column like such: =IF([@Date]"",167, 0). Hence if there is a date entered it will auto populate 167 in the Price column in the table, if it does not have a date inserted it just '$ - ". But when I try to SUM or IFSUM (=SUMIF([Date], "", [Price]) formulas it rather leaves a "$ - "or has the #### error. I have tried SUMPRODUCT, SUM, SUMIF, and SUMIFS formulas but cannot get it to total sum the columns that have a price compared to not having anything. Please help!

  11. I am trying to use the sumif function to sum a series of job #'s that are specific to salespeople. I am exporting this list out of our accounting software & I noticed that the job #'s are actually labeled as "text" in my worksheet. What would my criteria be for the following:

    Job Billed to Date

    2074-14 4,306.11
    2999-17 0.00
    4000-17 0.00
    4001-17 0.00
    4002-17 0.00
    4003-17 0.00
    4004-17 0.00
    4005-17 0.00
    4006-17 0.00
    4070-16 18,462.00
    4076-16 10,133.00
    4082-16 7,940.00
    4083-16 7,610.00
    4091-16 7,895.00
    4092-16 5,925.00
    4093-16 7,510.00
    4094-16 0.00
    4095-16 15,863.00
    4096-16 7,876.00
    4097-16 7,200.00
    4098-16 18,360.00
    4101-16 5,450.00
    4102-16 7,500.00
    4103-16 12,100.00
    4104-16 0.00
    4105-15 9,510.00
    4105-16 0.00
    4106-16 2,950.00
    4107-16 7,754.00
    4108-16 0.00
    4999-17 0.00
    5000-16 53,529.00
    5004-16 22,683.00
    5005-16 1,419,164.00
    5007-16 30,299.00
    5008-16 0.00
    5011-16 0.00
    5013-16 0.00
    5014-16 0.00
    5015-15 111,403.52
    5015-16 0.00
    5016-16 0.00
    5017-16 0.00
    5018-16 0.00
    5024-15 57,166.00
    5999-17 0.00

    Column "A" is the job#, column "B" is the billed amount. I am trying to sum the sales for column "A", which each range represents a salesperson. Ex., series 2000-2999 jobs = Bob, series 4000-4999 = Joe & series 5000-5999 = Marie.

    Here is the formula that I am trying to use, but it is not working. Any ideas? Thanks

    =SUMIF('Sep 16'!A2:A1048576,">2000,<2999-99",'Sep 16'!B2:B1048576)

    1. You could do:
      =SUMIF('Sep 16'!A2:A1048576,"2*",'Sep 16'!B2:B1048576) to sum up all job# starting with 2 for instance (repeat accordingly for 3, 4 and 5s or build up a formula like so: C2&"*" for your criteria)

  12. hi, just suppose we have 2 list. in first we have customers account and pending amount. in second list of some customers with account who have paid partial of full amount. then how to find net amount? if list are large and we cant sort them..

  13. Great article! I thought I knew SUMIF but I learnt a lot of things there.

    Similarly to sum-range indicating only the upperleft-most cell, it would seem that range actually indicates only the upperright-most cell is that correct ?

    I stumbled upon a spreadsheet that I couldn't understand how, was giving the correct result, basically instead of =SUMIF($B$2:$B$10,$H$2:$H$10,$D$2:$D:$10) it read =SUMIF($A$2:$B$10,$H$2:$H$10,$C$2:$C:$10)
    I now understand the shift to column C in 'sum_range' as 'range' was 2 columns wide, but I wanted to confirm the behavior of 'range'.
    Thanks!

  14. how to sum up c1:c50 when b1:b50 is not o or c or oc?

  15. Hi, Great blog and I'm hoping you can help?

    I have a large sheet called 'Errors' (11 columns) and in Column A I have employee names who have recorded till errors over 2016, so their names appear multiple times down the list and the value of the error short/over appears in Column J.

    In a separate tab I have the full list of employee names, so for example A1 is Adam. In B1 I'm trying to create a formula that will return all shortages created by Adam in 2016 by looking at the other sheet (A1:K4000. I've been trying something like =VLOOKUP(A1,ErrorsA1:K4000,10,"<0) but it returns a #value error.

    Any help would be greatly appreciated.

    Darren

  16. This blog is phenomenal. I am stumped. I have an account number for my customers is column A and Column B lists the sale amount. I would like to have column c aggregate the sales for each customer. Some customers have 1 sale each month some have 10. How do I get a running total for customers in column c

    1. Hi DallasJewel,

      Is my understanding correct that there can be several occurrences of the same account number in column A if a customer has more than 1 sale? If so, you can use this formula:
      =SUMIF(A:A, A1, B:B)

      If you are looking for something different, please clarify.

      1. what do you means different,

  17. What a great Blog!

    Im not great at excel but your explanations are so simple and easy to understand!!!

    I do have a question though :-p I am currently using you're SUMIF formula for specific text.

    =SUMIF('Main Tracker'!G7:G30,"*GBCDF*",'Main Tracker'!T7:T30)

    I was wondering can you also add a date range to that formula so that it totals up everything with "GBCDF" within a certain date range.

  18. Hello,

    Here is my questions with the example

    I have in F2 = "Apples/Bananas"
    I need my formula to be =sumif(A2:A8,F2,C2:C8)

    In F2 I can have whatever sign or space that make this formula works.
    I need this formula to add Apples and Bananas.

    Any thought or help ?

    Thank you in advance
    C.

  19. I can't seem to get this to work. Is this formula legal or possible?

    =SUMIF('ITSC-0101S-01:ITSC-0909S-01'!E3, B3, 'ITSC-0101S-01:ITSC-0909S-01'!B3)

    What is supposed to do is look at cell E3 across multiple sheets and IF it matches cell B3 on the formula sheet then add the corresponding cell B3s together.

  20. Dear
    Greetings..!

    I'm trying to calculate SUMIF function but i do not know how to calculate and getting my answer but i would expecting you guys will solve my problem in excel.

    i have an excel sheet that contain in Column (A) Date as (2-12-2016 9:37 PM) in Column (B) Names Like (Adam, John, Wiki) in Column (C) an Amount such as 100, 200, 500 in Column (D) Should be My Answer that's are follows

    if in Column (A) Date "1-1-2016 to 31-1-2016" and in Column (B) "Adam" then Calculate the amount in Column (C) "100" and show the Addition in Column (D)

    i tried this but i'm fail

    =SUMIFS(C1:C3,B1:B3,"Adam")+SUMIFS(C1:C3,B1:B3,">=1-01-2016",A1:A3,"<=31-01-2016")

    Kindly Help me
    Thanks in Advance

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. how can I

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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