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

  1. What is the formula if i want total sum of 1 cell will not exceed 29. The excess will carry over to the next cell if reach the total at 30 ,automatically converted into 1 count which the value carry over.
    Thanks

    1. Hi!
      To do calculations by condition, use the IF function.
      For example:

      =IF(SUM(A2:A10)>29,29,SUM(A2:A10))
      =IF(SUM(A2:A10)>29,SUM(A2:A10)-29,0)

      Hope this is what you need.

  2. i have 10 invoices, want to sum only those invoices whose sum is nearer to certain value. e.g.

    Invoice Value Nearest sum required less than or equals to 1000
    A-1 100
    A-2 350
    A-3 240
    A-4 370
    A-5 400
    A-6 110
    A-7 50 50
    A-8 900
    A-9 950 950
    A-10 700

    from above A-7 & A-9 invoice sum is less than or equals to 1000

    please suggest any formula

  3. i have 10 invoices, i want to sum invoices (it may be 1 invoice or multiple) whose sum will be nearest to certain limit. how to do ?

  4. How sum if
    A B C
    20 30 40
    10 50 90
    40 60 30
    70 30 60
    In the above eg. I want a sum of column C from the Min or Max value of column A or B. As A2 has the first occurrence of min/max value so from there I want the sum of the C column (90+30+60) C2 to C4.
    I m giving another for better understanding the query
    A B C
    20 90 30
    10 30 40
    20 10 50
    50 30 50
    10 50 20
    in this eg. max or min value from column A & column B which appears/comes first is B1 (90). So from B1, I want the sum in column C, from C1 to C5 (30+40+50+50+20)
    Please help with this question.

  5. Hi

    I have studied the above, and it works well,
    I am stuck on when I need to sum up a weekly column but avoid entering the date range every time.

    =SUMIFS('TO PHL FROM UK'!H:H, 'TO PHL FROM UK'!O:O, ">=16/01/2023", 'TO PHL FROM UK'!O:O, "<=22/01/2023")

    I would like to be able to drag the formula down so the date range stays 7 days but moves along to 23/1/2023 - 29/1/2023 (UK date format)

    please kindly advise

    thanks

  6. Hi
    Can you assist with a formula for me..

    if the name in column A (NAME) is the same, add the amounts for that name from column B (BILL) and provide a sum total in column C (TOTAL BILLS)
    The first list below is the data, and further below is my expected results

    Name Bill TOTAL BILLS
    Lewis £100.00
    Lewis £150.00
    Abby £20.00
    Abby £30.00
    Abby £50.00
    Abby £10.00
    Martin £80.00
    Martin £105.00

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    Name Bill TOTAL BILLS
    Lewis £100.00 £250.00
    Lewis £150.00
    Abby £20.00 £110.00
    Abby £30.00
    Abby £50.00
    Abby £10.00
    Martin £80.00 £185.00
    Martin £105.00

    Thanks so much

    1. Hi
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

  7. I have two columns of numbers, A and B. I want to Sum the numbers in column B where the corresponding number in A is not "" (that parts easy) and where the corresponding number in A is greater than the number in B and I want to do this all in a formula in one cell. Is that possible with Summifs using Google Sheets?

  8. I am trying to display a total (sum) from 3 cells which haven't had data entered yet so they are currently blank. I don't want the result to be a "0" unless information entered into any of these 3 cells total "0", I want the result to be blank if all 3 cells are blank.

  9. I have data in column B2:B100 describing a sinusoidally oscillating signal oscillating about 0. The amplitude of the oscillations are either increasing or decreasing amplitude, and I want to find:

    1) the values of the Peaks in column F (like E1says "first Peak" and F1 contains magnitude of first peak.
    2) the values of the Valleys in column H (like G1says "first Valley" and H1 contains magnitude of first Valley.

    How do I set up the excel statements? Appreciate your help.

    Yogi Dayal

  10. Hi , I'm trying to make a formula for today profit , so it updates every day based on today date I'm putting it as =sumif(range,"today()",sum_range And for sum reason it always shows $0 , do you know what the formula should be in this case?

  11. I want Cell D17 to perform equation (D21-C21*100) but only if E21 and E22 are >=100%.

    1. Hi!
      To use multiple conditions in a formula, use the AND operator in the IF function. In this article, you will find a detailed description and examples.

      IF(AND(E21 >=100%, E22 >=100%), D21-C21*100,"")

  12. Hi I am in the process of completing MOS 201 Objective 3.1. Not sure if you are familiar with the book but I cannot seem to figure out this sub objective. I tried different ways and I had gotten the answers but I am still getting (DiV/0) which should not be.

    On the Gross Margin worksheet, add formulas to the Gross Margin field (H7:H14) that calculate the gross margin by subtracting Cost from Retail and then dividing by Cost. To avoid #DIV/0! (division by 0) errors, wrap the calculation inside an IF function that returns the gross margin if Cost is not 0, or the message Cost is 0! otherwise.

  13. trying to create a Gannt chart. part of this is a list of tasks and sub tasks which are assigned numbers e.g 3 and this might have a sub task 3.1 which again may have a sub task 3.1.1
    to each task or sub task is assigned a duration in days. the duration will be assigned to the lowest sub task in this case task 3.1.1 So i want a formula that will sum up the duration for the subtasks that fall under its "jurisdiction" so for subtask 3.1 it would include durations assigned to task 3.1.1, 3.1.2.... 3.1.n but not any time associated with any other subtasks under task heading like 3.2 or 3.2.1 or 4 or 4.1.
    i would like the formula to be able to be dragged to autofill cells beneath and the formula still work

  14. I need to sum a vast list but only three columns. A is the technician name, B is the cost of labor, C is the invoice. One invoice can have a dozen entries and I just need the total for that invoice. I'm a novice, thanks.

    JOE SMITH 25.00 338117
    JOE SMITH 5.00 338117
    JOE SMITH 12.50 338118
    JOE SMITH 262.50 338150
    JOE SMITH 57.50 338160
    JOE SMITH 12.50 338160
    JOE SMITH 7.50 338160
    JOE SMITH 5.00 338160
    JOE SMITH -2.50 338160
    JOHN JONES 22.50 338161
    JOHN JONES 12.50 338161
    JOHN JONES 10.00 338161
    JOHN JONES 5.00 338161

    1. Hello!
      Use the SUMIF function to calculate the invoice amount:

      =SUMIF(C3:C10,338117,B3:B10)

      This should solve your task.

      1. Thank you, I would have to enter it for every invoice? I'm simply highlighting each section to get the total now.

  15. I want to sum the values in column B which satisfies that any data in column A is greater than 7 but less than 13.
    How do i write the formular?
    Kindly help.
    Thanks.

  16. i want to multiply 2700 if total days is less than 14 and greater than equal to 14 is multiply by 4000 please help

  17. My issues is that I need to calculate several cells together but only if a name exists in the first cell of the row,
    B C D E F G H
    12 kl 50 28 13 18.1 50.5. TOTAL

    so B12 would be a name and if no name appears in that cell then there is no total

    1. this is what I am working with

      B19 c19 D19 E19 F19 G19 H19 I19 J19 K19 L19 M19 O19 P19 Q19
      JOSH 50 28 13 18.1 50.5 10 20.50 9.16 =sum of c19:p19

      meg 50 28 13 18.1 50.5 10 20.50 9.16 =sum of c20:p20

      I need the sum of c:q for the total of monthly fees. I only want it to total if there is a name in the B column. The name will change in each row.

      I tried the first formula and I am getting a blank space

    2. Hello!
      To find the sum for a row with a condition, use something like the following formula

      =IF(B12="Name",SUM(C12:H12),"")

      If this is not what you wanted, please describe the problem in more detail.

  18. =SUMIFS(G22:G114,$K$22:$K$114,2022)

    What if I want to take the value in G:22:G114 and divide it by a number in another column, say N:22:N114, before adding.

    The value in the N could be different for each row. So in this example if the value for G26 is 1000 and the value for N26 is 4 then it would only add 250

    The next row could have a value of 400 in the G column and a 2 in the N column and it would add 200.

    So at this point I would have a value of 450.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUMPRODUCT(G22:G114/$K$22:$K$114)

  19. How to use SumIF formula to calculate commission
    Problem: Minimum Guarantee is 1000 or 10% of sales whichever is higher. Please explain how to use the formula. Thanks

  20. I want just to thank you for explaining the use of asterisk in condition of SUMIF to find certain word in a string (e.g. "*apple*"). A discovery for me :-)

  21. for Example
    item id item name Qnty Stock
    47158 Whirlpool Double Door Refrigerator 340L IF INV CNV 355 3S Arctic Steel 1 Yes
    47435 Avast Gift Voucher Premium Security MRP 1499 1 No
    i want to only yes quantity in other sheet against item id please help

  22. if i got cell A1 to AA1,but i only require to sum every 1,3,5,7,9....column
    How should i set the sumif formula?

    Thanks in advance

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUMPRODUCT($A$1:$AA$1*ISODD(COLUMN($A$1:$AA$1)))

      Hope this is what you need.

  23. Is it possible ta change the cell color of two cells when these two cells are greater than 70% when summed together?

  24. SUMIF question. I have this formula which works fine. =SUMIF(E4:E319,">.2",C4:C319). However, I would like the ">.2" part of the formula to be in a cell where you can change the value from .2 to whatever you would like. Is that possible?

    Thanks in advance!

    1. Hello!
      Change the formula, replace the condition with a cell reference

      =SUMIF(E4:E319,D1,C4:C319)

      In D1 -- ”>.2″

      I hope it’ll be helpful.

  25. I want to use sumif function where input range to evaluate is output of Vlookup function, but it does not work as the output of sumif is always coming "0"

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  26. Hi,
    I have data of purchases of several different type of items on different dates and different quantities throughout the year. I want to apply such formula that I get a report in which it gives total purchase quantity for particular period, say Aug 15 to Oct 10, for the item written in A1 cells.

  27. Sir,
    Thank you. Please give one day. Tomorrow I will confirm.

  28. Sir, Thank you very much for your support. My query is below.
    10001 10003 10005 10006 10007 Total
    7712 398 250 125 4700 13185
    5784 299 250 325 4667 11325
    1) I want to sum if the Header (Criteria) 10001,10005
    2) I want to sum if the header "define name" - Criteria used defined name

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(((A1:E1=10001)+(A1:E1=10005))*A2:E10)

      If there is anything else I can help you with, please let me know.

  29. I write sumifs function as below with multiple criteria. But showing error.
    Kindly advise to solve.

    =SUM(IF(K1:BE1{"10001","10003"},K134:BE134,0))
    =SUMIFS(K134:BE134,K1:BE1,"10001,10003")
    =SUMIFS(K134:BE134,K1:BE1, hcode)
    hcode as range
    hcode contains 10001,10003

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please specify what you were trying to find, what formula you used and what problem or error occurred.The first formula violates the syntax rules of the IF function. The data ranges in the SUMIFS function must be the same size. You also violated the syntax rules. Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

  30. Hi, I'd really appreciate your help with something.
    I'm trying to work out the number of days between 2 dates, but if there is no date in column B, I would like column C to read "Not Seen". Here's an example of a date in column B and then no date in column B.
    Column A Column B Column C
    1/6/2020 6/6/2020 5 (using formula =B2-A2)
    1/6/2020 -45778 (this is what's happening now)
    1/6/2020 Not Seen (this is what I would like to have)

    Thanks in advance

    1. Hello Bec!
      If I got you right, the formula below will help you with your task:

      =IF(B2<>"",B2-A2,"Not Seen")

      I hope it’ll be helpful.

  31. =SUMIF($A$17:$A$28,A2&">0",$D$17:$D$28) is it Correct ">0"

    Plus Valuve Minus Valuve
    01/07/2020 - -
    02/07/2020 - -
    03/07/2020 - -
    04/07/2020 - -
    05/07/2020 - -
    06/07/2020 - -
    07/07/2020 - -
    08/07/2020 - -

    01/07/2020 $1,000.00
    01/07/2020 $2,000.00
    01/07/2020 $5,000.00
    02/07/2020 $(500.00)
    02/07/2020 $(10,000.00)

  32. so I have a simple thing that for some reason I cannot make happen. I need to sum a range of cells, but I need to only show the value of the cell that is 12 or less in the cell, but still allow the next cell to show what is over 12.
    this is a payroll issue to separated out overtime...
    so.. we have 4 areas that employees can work hours, I need to total those 4 cells and have only the first 12 hrs actually show in the reg hours worked and then anything over 12 will show in the overtime.
    Seems simple -- but so not for me!

    1. Hello Nancy!
      If I understand your task correctly, in cell F1 write down the following formula:

      =IF(SUM(B1:E1) > 12,12,SUM(B1:E1))

      And in cell G1 write down the following formula:

      =IF(SUM(B1:E1) > 12,SUM(B1:E1)-12,0)

      I hope it’ll be helpful.

  33. Here Khata Column followed by "do" I want to add Area Field

    Khata Plot Area Rent Cess
    1 361 0.18 5.00 3.00
    do 367 0.155 5.00 2.50
    do 360 0.165 5.00 2.50
    2 413 0.068 0.80 0.50
    3 348 1.128 13.80 10.50
    5 206 0.63 6.30 3.00
    6 253 0.065 6.45 3.00
    do 254 0.085 6.45 3.00
    do 257 0.08 6.45 3.00
    do 255 0.025 6.45 3.00
    do 256 0.008 6.45 3.00
    do 250 0.138 6.45 3.00
    do 251 0.066 6.45 3.00
    do 252 0.03 6.45 3.00

  34. I need to calculate the following table
    f 2
    m 1
    m 5
    f 2
    f 4
    f 1
    m 0
    m 1
    f 1
    in this two columns, column 1 = m & f, column 2 = figures, i need how many 0-5 f's in figures column and 0-5 m's. no need the total need only the number of, please help

  35. Hi, I am currently using the "SUMIF in combination with the TODAY()". I have a problem with the weekends. If I use the "<"&Today(), unfortunately on the 06.01.2020 no value is provided. Is there a way how i can exclude the weekends in the formula and still obtain the values? I wish to avoid changing the formula manually. I rather have a formula which I can drag and obtain the correct values. Thanking you in advance for your help.
    03.01.2020
    06.01.2020
    07.01.2020
    08.01.2020
    09.01.2020
    10.01.2020

  36. =IF(AND(TEXT('Outbound data'!K:K,"0")="TFS-01.MATE,KHM.ALI",TEXT('Outbound data'!L:L,"0")="110018144"),SUMIF('Outbound data'!L:R,$E5,'Outbound data'!R:R),"0")
    I want to compare two places if two places value match then respective line next row value sum should appear.

  37. Dear Sir/Madam,
    I want to filter some text but I can,t. Please help in this matter. The need is.... I have pending customers list. And the Heading is "Serial NO., Name, Mobile No., Due month& Year".

    I want filter the Year before 2017 and don't Show the customer Details entire the serial number linked with the Year I filter. Please help me.

  38. hi i dont know if this is the right place to ask but. i have some columns that has either 1 or 0 in them and i need to add up the 1 up to the first 0 and no further.problem is there may be hundreds of 0 in the same column. and the same for 1. so my question is how can i add up all the 1 up to the first 0 and nothing else. ive been doing it manually but it is annoying plus the chance for error is bigger.

  39. Sir I need a help.
    I want to addition 2 column with a condition ie
    need calculation in U4,condition is that if column no "N4" is >= column No F4, need 0 if not, need original amount while adding N4+T4

  40. Sir I need a help.
    I want to addition 2 column with a condition ie
    need calculation in U4,condition is that if column no "N4" is >= column No F4, need 0 if not, need original amount while adding N4+T4

    1. hi,i am struggling with calculating data.
      how to calculate if range of specified text is met then sum range greater than 0 in one cell.
      eg. IF(A2:A50,"abc") THEN SUMIF(F2:J15,"<0")

      Thank you for looking in my post.

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

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

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

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

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

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

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

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

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

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

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