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

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

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

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

  4. 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,"")

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

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

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

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

  9. =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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hello,

    Column A i have date starting from 1 to 30 up to rows 30...

    Column B i have Day from Monday to Saturday up to rows 30...

    Column C i have Traget hours from 16 to 50 hours with if logic function base on value of column F ( Total of column D and E )

    Now i want to apply sumif formula at the end for day for Example if the day is Friday then ...

    =SUMIF(A1:A30,"Friday",B1:B30 )

    But formula not given any error or and doinn sum also..

    But same formula when I apply in column those have direct value ( not coming from if fuction ) it's working properly.

    Thanks if anyone can give anwser

    1. Hello Hari Mohan,

      As you have dates in column A, days in column B, and the hours to sum in column C, your formula should have columns B and C as the range and sum range instead of columns A and B, i.e.:
      =SUMIF(B1:B30,"Friday",C1:C30)

  32. Your help with my problem would be appreciated.
    It is probably a simple code, but I'm struggling with it.

    Every 4 years I have to calculate how many precinct committee people a precinct can have at each site.

    For less than 1,000 voters, it is 1 pair
    For more than 1,001 voters, it is 2 pair
    For more than 2001 voters, it is 3 pair

    The form is a 3 columns: first column is the name of the precinct, 2nd column is the total voters and 3rd column shows how many precinct committee people is allowed.

    Precinct Name Total Reg Voters Allowable Pairs
    Sumter Place Rec Room 3432

    Can you help?

    1. Hello Jeannie,

      I'm really sorry that you had to wait for the formula.

      You can use the IF function with a combination of your conditions for your task:
      =IF(B2<1001,1, IF(B2<2001, 2, 3))

      You need to enter this formula into column C and copy it across the column.
      Here B2 is the cell with the "total reg voters". If there are fewer than 1001, the formula will show 1, if there are fewer than 2001, it will show 2, otherwise it will show 3.

    2. Since I have not heard back on my question, I can only assume
      that there isn't a formula?

      Jeannie

  33. OK. My prior post somehow did not come across what I had written. Trying again

    Create a formula with sumif(s) using >0 or <0 in the equation. Keeps returning either a "false", or sum of entire range.

    sum range = BSJun_Act
    criteria = BSMap_to,"Interco"

    1. Hello Diana,

      It sounds like you need to use the SUMIFS function that lets you specify several conditions. E.g.
      =SUMIFS(B15:B27,A15:A27,"Interco",B15:B27,">0")
      The range A15:A27 is checked for the word "Interco", B15:B27 is checked for the condition ">0".

      You can use named ranges instead of the range references by spelling them this way:
      =SUMIFS(BSJun_Act,BSMap_to,"Interco",BSJun_Act,">0")

      If you get an error, please make sure both ranges are of the same size.

  34. Hi, Svetlana,

    I am kind of trying to right down a formula that would count me the following condition:

    - if the certain cells sum (lets say B2 to Q2) would be more or equal to 80, then the sums of the cells B2:B3 should be multiplied by 2 and to it cells B4 and B5 should be added, if the cells sum (B2 to Q2) would be more or equal to 160, then the sums of the cells B2:B3 should be multiplied by 3 and to it cells B4 and B5 should be added, if neither are correct (that is the value of the cells sum is less than 80) then the formula should return value D5.

    Even better formula would be:

    - when the sum of the cells reaches number 80, then the sum of the cells B2:B3 should be multiplies by 2 and to it cells B4 and B5 added, when reaches 160 - multiplied by 3 and to it cells B4 and B5 added. If the sum is less then 80, then the value should be D5.

    I can't get correct formula anyhow...

    Would really appreciate Your insights on this one. Thanks in advance.

    1. Hello Justin,

      You can use the following formula for your task:
      =IF(SUM(B2:Q2)>160,SUM($B$2:$B$3)*3+SUM($B$4:$B$5),IF(SUM(B2:Q2)>80,SUM($B$2:$B$3)*2+SUM($B$4:$B$5),D5))

      If you'd like the summed ranges to shift as you copy the formula, please make the references relative, i.e.:
      =IF(SUM(B2:Q2)>160,SUM(B2:B3)*3+SUM(B4:B5),IF(SUM(B2:Q2)>80,SUM(B2:B3)*2+SUM(B4:B5),D5))

  35. How to sort data when cell are merged and wraped

    1. Hello,
      You can sort merged cells only if they are of the same size: select them and click the Sort icon.
      Otherwise you need to unmerge all cells in the range before sorting.

  36. Hi?

    Kindly help i have data of overheads with diferent dates now i want to sum up using the sum if functions overheads per month eg

    Electricity 2/04/2015 $200
    electricity 3/04/2015 $215
    courier cost 2/04/2015 $2

    Basically i want to come up with a spreadsheet that can be able to sum up overheads say march electricity was eg $700 travel Expenses may $600

    Thanks in advance

    Bismark

    1. Hello Bismark,

      If you want to consider just the month, you can use one of the following formulas to calculate the expenses, e.g. in March:

      =SUMIF(B1:B3,">="&DATE(2015,3,1),C1:C3)-SUMIF(B1:B3,">="&DATE(2015,3,31),C1:C3)

      =SUMIF(B1:B3,">=03/01/2015",C1:C3)-SUMIF(B9:B14,">=03/31/2015",C1:C3)

      =SUMIFS(C1:C3,B1:B3,">=03/01/2015",B1:B3,"<=03/31/2015")

      If you want to consider the value in column A as well, e.g. "electricity", please use the SUMIFS function:
      =SUMIFS(C1:C6,B1:B6,">=03/01/2015",B1:B6,"<=03/31/2015",A1:A6,"electricity")

  37. Hi,

    I have a spreadsheet where I am trying to work out a formula to tell me how many cells have a date that is older than a year from today. At the bottom of the training date column I'd like to see a figure that tells me how many of the cells have a date older than one year.
    Are you able to help please?

    Training Date

    01/06/2014
    03/06/2015
    01/01/2014
    01/10/2014
    05/06/2014
    09/08/2014
    01/03/2014

    1. Hi Rae,

      You can use the DATEDIF function to calculate the number of complete years between the dates in, say, column A and TODAY(). And then add up those that are equal to or greater than 1:
      =SUMPRODUCT((DATEDIF(A2:A100, TODAY(),"y")>=1)*1)

  38. I'm not sure if I have the right topic, but I'm trying to subtract the larger number from 2 cells (not a range, eg. A1 and A3) and subtract them from a number in cell A5. I just can't seem to find a formula that works.

    1. Hi Sean,

      Here you go:
      =IF(A1>A3, A5-A1, IF(A3>A1, A5-A3, ""))

      Just notice that the formula will return an empty string if A1=A3.

      1. Thank you so much!

  39. How would I get the sum formula to sum two numbers. I need the smallest number out of Coulum O through R and need that to be added with the number from column S. I just need the sum formula to decipher the smallest number from the range of colums.

    1. Hi beth,

      You can use a formula similar to this:
      =MIN(O2:R10)+MIN(S2:S10)

  40. I am wanting to sum cumulative values across cells if the last cell in each formula is greater than 0.

    e.g sumif(C18:F18,F18>0)
    F18 being the last cell in the formula, and the next one would be (C18:G18,G18>0) and so on

    This is for an actuals vs forecasted spend graph of invoices.

    1. Hi Sarah,

      I believe you can use this one:
      =if(F18>0, SUM($C$18:F18), "")

      1. That works perfectly, thank you! :)

  41. I am trying to sum numbers that occur at specific times (4/20/15 2:01 PM, 2658
    4/20/15 2:04 PM, 2268, etc.)
    but I only want to sum them if they occur during another time window specific in a third column. Is there a way to do this? The goal is to try to take random time periods and make them more uniform (15 minute intervals) by summing results during the random time period.

    1. Hi Rebecca,

      You can try using the COUNTIFS formula similar to this:

      =COUNTIFS(A1:A11, "04/20/2015 2:01:00 PM", B1:B11, 10)

      Where column A contains times, B - intervals, and 10 is the time interval you want to count.

  42. I want to find the sum of the QUANTITY of any row that has a particular text string present in 1 or more columns of that row.

    Example:

    Row 1 has a quantity of 1,000 and has the word:
    "Apple" in 2 of the 5 criteria columns
    "Plum" in 1 of the 5 criteria columns

    Row 2 has a quantity of 2,000 and has the word:
    "Apple" in 1 of the 5 criteria columns
    "Plum" in 0 of the 5 criteria columns

    The resulting quantity for "Apple" would be 1,000 + 2,000 because both Rows 1 and 2 have the word "Apple" in at least one of the 5 criteria columns.

    The resulting quantity for "Plum" would be 2,000 because only Row 2 has the word "Plum" in any of the 5 criteria columns.

    1. Hi Dianne,

      You can add up 5 SUMIF functions, like this:

      =SUMIF(B2:B100, "apple", A2:A100) + SUMIF(C2:C100, "apple", A2:A100) + SUMIF(D2:D100, "apple", A2:A100) + etc.

  43. Hi,

    Reg SUMIFS formula.

    My criteria range include numbers but they are in text format. Such as 00001,00002. Since these numbers are coming from ERP system report, format changing is quite difficult. Please let me know a solution for this. Sum range is OK. My formula is not working because criteria range is in text format. please help.

    1. Hi Rasika,

      Did you try enclosing those text-numbers in double quotes like usual text values? E.g.:

      =SUMIF(range, "00001", sum_range)

  44. I have a column of a range of dollars and another column with a range of hours.
    I want to have a function whereby if the dollars fall between two values then it returns of a sum of the corresponding hours.

    I.E if the $ are equal to or between $3000 and $5000 then add the hours in the Hours column which correspond to the dollar rows.

    I am sure this can be done - but how?

    Best wishes

    RJ

    1. Hello Rupert,

      You can use the SUMIFS function to sum values in the column with hours when the dollar values fall between two numbers:
      =SUMIFS(B12:B20,A12:A20,">3000",A12:A20,"<5000")

      Here column B is the one with hours and column A contains the range of dollars.

  45. Hi, I have three columns, A, B and C:

    Col A
    36
    0
    253

    Col B
    2
    0
    3

    For Column C, I need a formula that will provide the sum of the corresponding cell in Column B, except if the corresponding (row) number in Column A is between 0 and 43. In that case, the number in Column C should be a "1".

    So instead of Column C being 2, 0, 3, it would be 1, 0, 3. Can you possibly help??

    Thank you in advance.

    1. Hello Lory,

      If we understand your task correctly, here is the formula you need:
      =IF(AND(A1>0,A1<43),1,B1)

  46. Hi,
    I am trying for a formula where I am summing of cells and it 8th not showing the exact value due to negative value. I don't want to count the cell in negative or #ref!.Please help me with formula.

    1. Hello Deepak,

      You can use the following formula to exclude negative numbers:
      =SUMIF(B1:B8,">=0",B1:B8)
      Here B1:B8 is the range of cells you want to sum.

  47. First, let me say thanks for and good work on a well written tutorial. I found this page by googling "excel sumifs not equal to text" and it has been helpful, but I'm not quite to what I need.

    Suppose in your example "Using comparison operators with cell references" that you wished to sum items not in a range instead of not in a single cell - how would the syntax change instead of ""&F1 ? If column G had a list of 3 different fruit, is there a way to do G1:G3?

    I tried a couple things, and they didn't seem to work. I could achieve the desired result by using a sumifs statement with each individual cell called out in an additional criterion, but if I had a list with more than just a few cells, adding several criteria would quickly become tedious. Any advice would be great. Thanks!

    1. Hi Greg,

      Thank you so much for your kind words. I am afraid I cannot suggest any way other than a SUMIFS formul such as:
      =SUMIFS($B$2:$B$9, $A$2:$A$9, "<>"&G1, $A$2:$A$9, "<>"&G2, $A$2:$A$9, "<>"&G3)

      1. Thank you for the response! I've used SUMIFS to get this affect, but my concern is that if column G has 20 entries, the formula will become rather long (or, because the entire column cannot be excluded, adding an entry will require a change to the formula). After some further research, it looks like I'll be able to get there with a complicated SUMPRODUCT statement including ISNUMBER and MATCH. I will try that for my application and see where it goes.

        Thanks again!

  48. Is this possible?
    I want to sum amounts from criteria contained in more than one column:
    1. Criteria = is equal to a credit card number (This column that contains the credit card number)
    2. Criteria = is equal to “NO” (This column contains [yes or no] and it indicates whether or not the card has been paid)
    3. Sum_Range = I want to include the “No” criteria sums

  49. I have a table with a column for insurance company, a column for type of insurance, and a column for the premium paid for that policy. I'd like to do a conditional total IF Column A = "Erie" AND Column B = "Auto" THEN print the sum of those cells in Column C that meet both criteria. I'm thinking that I need to nest functions, but not sure the correct syntax for excel. Thanks!

    1. Hi Shawnda,

      You can use the SUMIFS formula similar to this:
      =SUMIFS(C2:C100, A2:A100, "Erie", B2:B100, "Auto")

  50. Hi,
    Using your given example.

    Is it possible to copy to a cell all the products with the same quantity?

    In order to list every product with quantity higher then X.

    Thanks in advance.
    Best regards,
    José

    1. Hi Jose,

      The easiest way to go would be filtering your data by quantity and copying the records you get.

      You can also use the VBA code that one of our developers wrote for you; please follow these steps:
      - Open your Excel file
      - Press Alt+F11 on your keyboard;
      - Double-click the sheet name in the list and paste the code below, but change the reference to your data range and to the cell with the quanity criterion:

      Private Sub CommandButton2_Click()
      Range("F1:G10").Select
      Selection.ClearContents
      i = Range("D2").Value
      Range("A1:B10").Select
      Selection.AutoFilter
      ActiveSheet.Range("$A$1:$B$10").AutoFilter Field:=2, Criteria1:=i
      Range("A1:B10").Select
      Selection.Copy
      Range("F1").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      ActiveSheet.ShowAllData
      Selection.AutoFilter
      End Sub

      - Click Run.

      You can see a sample spreadsheet with this solution here.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)