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

  1. Thanks for posting this! Great information and clearly presented!

  2. Hello there,

    I need a formula to do the following...
    if column D contains today's date, and column F contains "apple" then SUM column E.

    Could you please help me with that... ?

  3. Thankyou so much for being help us. but here i want to ask how we can copy text statement from one sheet to another sheet so that if we changed in master sheet the change could appear in other sheet automatically.

  4. Thank you so much for this site. I was able to finally figure out the function that I needed and how to write the different parts.

  5. Just what I needed. Thanks for the detailed explanation.

  6. Hi, Please advise me an equation for following situation.
    I have a tracking table for tracking Design request enquires.
    i need to track on MONTHLY basis total number of Design Requests with Status (open / Close) and Overdue (Yes / No)
    Appreciate your support.

  7. Hi, how to combine both formulas in one cell?
    =SUM(H31,I31,J31)
    =IF(H31<I31,I31)+If(H31<J31,J31)
    I need to sum 3 coloums (H,I,J) but if I & J have values then H will need to subtract.
    Thanks & regards,
    Amy

  8. I wish to be able to allow a cell ( D6 )$ amount to be included in the column ( D1 to D20 )total if the next cell ( E6 )is marked with a check symbol ( wingdings 2 ). It said cell ( E6 ) is marked with an x ( wingdings 2 ), then the cell ( D6 ) with the $ is not included in total. And so on down the page for my monthly bills accounting.

  9. I'm trying to develop a mileage tracking sheet for both personal and business entries. Column A is the odometer when I arrive at a certain location. Column B will have a "Y" if the mileage is personal in that row. Column C is the total miles driven since the last stop if it is for business, no "Y" in column B for that row. Column D is the total miles driven since the last stop if it is for personal, "Y" in the B column for that row. Cell H2 contains a running total of business miles for the curent month. Cell H4 contains a running total of personal miles for the current month. I can't figure out how to subtract, for example, cell A5 from cell A6 using the SUMIF or SUMIFS function. I think I can write a formula for a running total for personal and business miles driven, but if you can suggest a formula I would appreciate it. Do I need to write a formula or include in the formula where to place the total miles driven for any certain row in either column C or D? Meaning for business miles driven on a single drive, no "Y" in column B for that row, and place that number in column C for that row and leave column D for that row blank. As you can tell, I'm struggling with these formulas. Thank you in advance for your help. DB

  10. Substantially important point below, don't think I've seen this nuance pointed out anywhere else!

    "The point is that Microsoft Excel does not rely on the user's ability to provide matching range and sum_range parameters, so to avoid possible inconsistency issues and prevent errors, it determines the sum range on its own in the following way. It takes the top left cell in the sum_range argument as the beginning cell (C2 in our Excel SUMIF example), and then includes as many columns and rows as contained in the range argument (in our case, it's 1 column x 7 rows, i.e. C2:C8)."

  11. So I'm running into some issues with a compound SUMIF formula.

    =SUMIF(AND(A8:A200,G8:K200,">"&Q1,G8:K200,""&0)

    The main issue is that there are missing values for the lines that have a 0 in the "count" column as the information is being pulled from another table where "Count" is compiled using a COUNTIF formula. This being the case, these rows have the #N/A value plugged in if the count column shows a 0 which prevents the data from calculating. Because of this, I am hoping someone may be able to help me out and suggest a way for me to add up the dollar values for any dates that appear in a column that is between Days 1 and 2.

    Essentially I need it to do the following:

    1. Check columns G for any dates within a 1week timeframe
    2. add up the values that have a quantity of 1 or more.

    does anyone have any suggestions?

  12. Thanks in advance.

    I want to use several Sumifs and all work except for the following.

    In column B there will be a list of numbers and I want the calculation to exclude all numbers that end with the number 2, is this possible? or the reverse add up all the numbers that don't end with the number 2.

    Phil

  13. How can i use Sumif statement with the condition or criteria is a highlighted cell? looking to pull cost based on Green, Yellow, or Red Cells. any help would be great.

  14. hi,

    i am maintaining invertory in excel, by the current date
    opening stock has to come with the total of previous for specific item.
    please guide the formula. my formate is as follows

    item Description 19-Jun-18 20-Jun-18 21-Jun-18 22-Jun-18
    Item No.1
    Item No.2

  15. I Want Help On following
    Tax Slab Rate of Tax
    - 400,000 0
    400,001 800,000 1,000
    800,001 1,200,000 2,000
    1,200,001 2,400,000 5% of exceeding of 1,200,000
    2,400,001 4,800,000 10% of exceeding of 2,400,000 & 60,000
    4,800,001 onwards 15% of exceeding of 4,800,000 & 300,000

    Taxable Income Is 3,880,000 Per year
    And Tax Liability 208,000 Per Yeat
    and i used the Following Formula for calculation
    =IF(C11<=B4,0,IF(C11<=A5,1000,IF(C11<=A6,2000,IF(C11<=A7,(C11-B6)*0.05,IF(C11<=A8,(C11-B7)*0.1+60000,IF(C11<=A9,(C11-B8)*0.15+300000))))))

  16. I want to apply a simple IF formula

    A1-A2=A3
    10-12=0
    10-8=0
    IF the result is <0 A3 should show 0 else the actual value.

    For example A1 value is 10 and A2 value is 12 and I want result in A3 only 0 if the result is <0.

  17. Hello!

    I am looking to do a Sumifs based on the following conditions:
    Sum = "Good" Column
    Part = "A"
    Step = Smallest value for Part "A" assuming this can change depending on data pull.

    PART STEP GOOD
    A 10 10
    A 10 10
    B 30 5
    B 30 10
    B 50 15
    A 20 10
    B 60 8
    C 20 6
    C 40 10
    C 50 20
    A 30 30
    B 30 40
    C 60 20
    A 30 15

    Thank you, any suggestions would be much appreciated!

    1. Chris:
      Where the data is structured as your sample and is in F36:F49,D36:D49 and E36:E49 enter the "Part" in I36 and the "Step" in I37, then in I38 enter the formula:
      =SUMIFS(F36:F49,D36:D49,I36,E36:E49,I37)
      You can then enter the step and part you're interested in seeing in their respective cells and the Good will be displayed in I38.
      You'll probably want to enter their labels in H36, H37 and H38.

  18. Hi! Sir
    I am from Malegaon. I am businessman.
    I watched all videos on your youtube channel about excel. All videos are very useful but I have one problem.

    The problem is.........
    CONSIDER:

    this is [ ] first column in excel.
    this is ( ) second column in excel.

    In this [ ] type number.
    In this ( ) number should come automatically.

    In this [ ] column 0 to 80 is capacity and after 80 starts from 1.

    [45] (45) this is 0+45=45
    [80] (35) this is 80-45=35
    [25] (25) this is 0+25=25
    [50] (25) this is 50-25=25
    [10] (40) this is 80-50+10=40
    [60] (50) this is 60-10=50
    [10] (30) this is 80-70+10=30
    [45] (35) this is 45-10=35
    [75] (30) this is 75-45=30
    [15] (20) this is 80-75+15=20
    [50] (35) this is 50-15=35
    [20] (50) this is 80-50+20=50

  19. If Cell A says "POS" i need to get 3% of cell B & C at Cell D,
    If Cell A Says "NAG" i need to get 5% of Cell B & C at Cell D

    Could you please help me to find the formula?

  20. Invoice date at row number 1 is less than October 2014.Invoice Date should be less than Current Date and greater than October 2014.Error in Excel Sheet Data.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  46. THANK YOU FOR SHARING US KEEP UP.

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

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

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

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

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