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 →

Table of contents

Comments page 5. Total comments: 346

  1. Hi!
    I am working on 2 sheets, sheet 1 & 2 which I re-named GENERAL and SUMMARY.
    The GENERAL sheet is where I do my in & out of materials and the SUMMARY sheet is where I get the balance quantity of the materials.
    e.g. material code on column H and quantity on column K of the GENERAL sheet, then material code on column B and balance quantity on column E of the SUMMARY sheet. I tried =SUMIF(GENERAL!H4:$K4:B4, 3331, GENERAL!B4:$E4, 3331) but failed. Kindly help

  2. How to use sum or sumifs in this condition

    If value is is start to 1 and end to 130 and then restart to 1

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  3. Please, guide me
    i want to check master SKU primary volume sale distributor type wise by latest date. format mention below.

    Date Master SKU Distributor Type Primary Volume
    12/5/2017 Olper 200ml Hyperstar - ICA 365
    12/5/2017 Olper 200ml Makro/Metro –ICA 480
    12/6/2017 Olper 200ml Hyperstar - ICA 72
    12/7/2017 Olper 200ml Makro/Metro –ICA 38
    12/5/2017 OMUNG 200 ML Hyperstar - ICA 178
    12/5/2017 OMUNG 200 ML Makro/Metro –ICA 120
    12/6/2017 OMUNG 200 ML Hyperstar - ICA 58
    12/7/2017 OMUNG 200 ML Makro/Metro –ICA 48

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  4. How can I use the sum (small) Function when my values are in non contiguous cells and not in a range?

    1. Hello,

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

      =SUM(SMALL((B1,B3,B7,B9,B10),{1,2,3}))

      Hope it will help you.

  5. I have a Y / No column. I want to know how many Yes and how many No. I'm sure this is very simple, that I just can't break out of the simplicity of it.

    1. Hello,

      Please try the following formula:

      ="Yes - "&COUNTIF(A:A,"Y")&" No - "&COUNTIF(A:A,"No")

      Hope it will help you.

  6. Working on a school fundraiser. We offer items for $3 each or 5 for $10. Is there a formula for determining not only the total sold but also calculating the amount of monies that should have been collected?
    Thanks

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  7. Hello everyone,
    I have a formula which is like this

    =COUNTIFS('sheet1 '!W:W,">0",'sheet1 '!A:A,"Truck")

    which is count all data in column W (sheet1) larger than 0, which are called Truck in column A.

    Now, I want this formula to read the same information but by consider a range of dates in between 1st of October to 15th (I have dates in Column B).
    Any input is greatly appreciate.
    Thanks

  8. I have invoice no in one column its may repeating depend upon how many items in a invoice, i need total value of item that is present in same invoice no

  9. Hello

    How can able to calculate Commission as per ton
    conditions are following

    less than equal 60 ton will pay 100$ if more than 60ton additional tons will pay in 150$

    how to formulate for this case which function i need to use
    please help me someone

  10. Thank you in advance for any assistance.
    I'm trying to SUMIF the amounts in H if they occurred on a given day.

    Formula is =SUMIF(TEXT($A2:$A36, "mdyyyy"), TEXT(G41, "mdyyyy"), $H2:$H36)
    A2= 9/9/2017 (cell formatted as date)
    H2= $2,201.88 (cell formatted as currency)
    G41= 9/9/2017 (cell formatted as date)

    When I view the calculation via fx, everything looks fine, but the result is 0.

    Thank you again for any insight.

    Best,
    Darin

  11. I need SUMIF FORMULA for certain 'Entry Name' along with seperation of Date from 1 to 30.
    Example
    A NAME HOURS
    1.9.2017 ABC 2
    1.9.2017 ABC 3
    1.9.2017 XYZ 2
    2.9.2017 XYZ 1
    2.9.2017 ABC 2
    3.9.2017

    How can I get total hours of 1.9.2017 for ABC and XYZ seperately?

    DATE ABC XYZ
    1.9.2017 5 2
    2.9.2017 2 3

    I need total hours for ABC and XYZ on basis of dates and name entered.

    Email: surajkc938@gmail.com

    Thank You!!

  12. My spreadsheet has one cell (A1) that contains the "pot" of money. One column (cell A2) is for amounts drawn against it with another column (C2) having a balance. So the formula in C2 is =SUM(A1-A2).

    Another column is what was “actually spent”, so if I put a value in that one, I would like to have C2 deduct that instead of the original amount drawn. This column will only have a value if what was actually spent is more or less than original amount.

    I'm looking for a formula something like "=SUM(A1-A2) unless B2 has a value then use that instead of A2”?

  13. Cell C5 displays the sum of C1:C4
    If I want Cell C6 to show the total in excess of B5
    but if C5 is less than B5 I want it to show 0

    How can I do this?

  14. MLot# CN Customer Colour Fabric Gry Wt Rate Str Print
    CL-10531 Bal ES Apparel Navy HTR S/J 351.25 95
    CL-15558 Dyd Mass Apparels & Fabrics (PVT) Ltd. H/Grey 2Flc 981.00 50
    CL-16455 Bal Crown Textile Boardex 3Flc 622.00
    CL-16664 Dyd Salman Industries Wind Chime 2Flc 707.05 Yes
    CL-16732 Dyd Salman Industries Peach Whip 2Flc 711.70 Yes Yes
    CL-16900 Dyd Salman Industries H/Grey 2Flc 526.70 90 Yes
    CL-16901 Fnsh Salman Industries Plane Moul 2Flc 974.40 Yes Yes
    CL-16913 Dyd Pelikan Knitwear Blue Flot F/T 579.90 175 Yes
    CL-16990 Fnsh Salman Industries H/Grey 2Flc 978.95 90 Yes Yes
    CL-17058 Dyd M. R. Export H/Grey 3Flc 972.50 Yes
    CL-17192 Dyd Pelikan Knitwear Blue Flot F/T 742.00 175 Yes
    CL-17193 Dyd Zubisma Apparel Navy 3Flc 473.20 Yes Yes
    CL-17244 Dyd Pelikan Knitwear Black 3Flc 440.25 160 Yes
    CL-17250 Dyd M. R. Export Mid Night Navy 2Flc 794.35 Yes
    CL-17278 Dyd Pelikan Knitwear White 2Flc 1002.05 Yes Yes
    CL-17321 Dyd Pelikan Knitwear Charcoal 2Flc 315.00 140 Yes
    CL-17339 Dyd M. R. Export H/Bleach 3Flc 954.95 Yes

    Table[CN]="Dyd" And Table[Str]="Yes" And Table[Print]="Yes"
    Then Sum of Table[Gry Wt] * 20

    Result should be =
    711.7 * 20 + 473.2 * 20 + 1002.05 * 20 = 43,739

  15. Can you use the SUMIF to sort by date range and cell info?

  16. Hai

    I have three columns e.g.

    USA 10.08.2017 10.54
    India 10.08.2017 9.45
    USA 11.08.2017 6.54
    USA 12.08.2017 2.98
    India 12.08.2017 1.65

    Now if destination repeats then sum the current value plus value of same destination before 48 hours from current date

    Can you help.

    Thanks so much!

  17. Hello

    I am using my sumifs with 2 criteria
    Sumif(a2:a7, d2:d7, >8years, e2:e7, "m")

    The age (d2:d7)I am converting using datedif

    The formula works up to 9years 11 moths, as soon as the age is over 10 years, it does not add numbers in a2:a7

    Any help would be appreciated.
    Thanks

  18. Hi, i have problem with SUMIF function. My criteria is acrticle code (ex. 031285), and SUMIF bring me result from 031285 and 31285 so it is incorrect then. How to force SUMIF to bring only exactly code article? Thanks!

    1. i need force SUMIF to use my zeros in article code to criteria when finding exact value from that article code.

  19. I have multiple sheets with the same data and I am able to sum the corresponding data with =SUM(Leuzinger1:Sheet2!C3). Now I want to only sum the value in C3 from a sheet if the value in A1 of that sheet is 1. Can I use sumif? Basically, I need to select all the sheets where A1 == 1 and then sum the values of c3. Thanks for any help!

  20. Hi,
    I am trying to write a formula to add up the values in a previous year based on the equivalent amount of recorded days this year to show a Year on Year variance.

    I have a support worksheet that adds up the amount of days / cells that has had data inserted in this year to produce a number. (for example 18 days)

    I have a list of values separated by days from last year and i want to return a value based on 18 days worth of data from that year.

    Could you help please?

    1. Hello, Gareth,
      I'm afraid it's a bit difficult to assist you without some specific data. You can read this part of the article more attentively to sum up the dates, otherwise, please, give us more details on your task.

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

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

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

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

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

  26. THANK YOU FOR SHARING US KEEP UP.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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