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

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

  2. Hi,
    This is really very much helpful.

    I have a data of a period of one year(with Date mmddyyyy format). Is it possible to use the SUMIFS function to get the first criteria as month against the date range and one second criteria to get sum of expenses

    Thanking You...
    Tonis

    1. Hello Tonis,

      If we understand your task correctly, you can use either SUMIF function:
      =SUMIF(A2:A33,">="&DATE(2015,2,1),B2:B33)-SUMIF(A2:A33,">"&DATE(2015,3,1),B2:B33)

      or SUMIFS function:
      =SUMIFS(B2:B33,A2:A33,">="&DATE(2015,2,1),A2:A33,"<"&DATE(2015,3,1)) Here column B is the one with expenses, and column A contains the dates.

  3. is it possible to use 'sumif'formula to sum the similar colored cells?

  4. Hello

    I want to sum invoice ammounts that our clients owe us. Some are due payments in 90, 60 or 30 days from invoice date. The fields are: client name, invoice date, total invoice amount and invoice state. Example: ACME, $3.567, 12-25-2014, pending.

    Is there any way to have update owed amounts based on today() that should be paid because 90 days since invoice date have occurred?

    Thank you

    1. Hello Tomeu,

      You can use Conditional Formatting to highlight the necessary amounts: create a rule and choose to "Use a formula to determine which cells to format", enter the following one:
      =AND(TODAY()-B2>90,D2="pending")
      Here B2 is the invoice date and D2 is the invoice state.

      You can also use a helper column with the following formula to show the amount if both conditions are true:
      =IF(AND(TODAY()-B2>90,D2="pending"),C2)

  5. how can I use SUMIF to add numbers in a cell with out the letter as in 8T, here is my formula so far

    =SUMIF(C10:AG10,"<8",C13:AG13) here is the problem.

    in C10-AG10 I have a number 8 or less, in C13-AG13 I have a number and letter 8T, 7T, 6T, 5T, 4T,... down to 0.5T and I need to add the number but not the "T", how can I modify the SUMIF statement to do this, the cells are formatted Text, I have put the cells to number but if I put in the "T" then it doesn't work, I need the letter to identify what kind of number it is.

    1. Hello Mac,

      Try the following array formula (press Ctrl+Shift+Enter to complete it)
      =IFERROR(SUM( --(C10:AG10<8)*(IF(LEN(C13:AG13)>1,LEFT(C13:AG13,LEN(C13:AG13)-1),0))),0)

      If it is not the case, you can send your workbook to support@ablebits.com. We’ll look into the issue and try to help.

  6. i have a table basically column A date, column B cash, column c Check. I need to look at column B & C in same row and sum all based on whether column A's year text only, like 2015.so it would give me a total of all payouts in 2015 only. the date column is formatted 1-jul-2015 with multiple years being listed.

    date cash check
    1-jul-2015 50.00 0
    1/jan-2014 0 100
    1-feb-2105 0 75

    total for 2015 should be 125.00

    1. Hello Brian,

      As SUMIF and SUMIFS sum values only if the ranges are of the same size, you can add two SUMIFS:

      =SUMIFS(B1:B3,A1:A3, ">=1/01/2015",A1:A3, "<=12/31/2015")+SUMIFS(C1:C3,A1:A3, ">=1/01/2015",A1:A3, "<=12/31/2015")
      Here B1:B3 is the range with cash amounts, C1:C3 is the range with check amounts, and A1:A3 is the range with dates.

  7. I'm trying figure out what formula to use. I have two columns of numbers. I want a formula that does the following:

    Whatever cell in column A is in the same row as column B, when the sum in column B whichever cell is greater than or equal to the value of another cell). ***SUM(B1:B-whichever cell is greater than or equal to the value of another cell)***

    Thanks!

    1. Hello Grant,

      Could you please send a sample worksheet with the data and the expected results to support@ablebits.com? It will help us understand your task better.

      Please include the name of the blog post and the number of your comment.

  8. Hi,
    I am working on a financial model, and i have may cash flow mapped monthly.
    Now i would like to analyze the information on a trimester basis.
    Please can you assist with a formula that allows me to sum the monthly amount to a trimester amount.

    Regards

    1. Hello Thany,

      You can create SUMIFS formula and use date ranges as criteria:
      Trimester 1:
      =SUMIFS(B1:B3,A1:A3, ">=1/01/2015",A1:A3, "<=01/10/2015")

      Trimester 2:
      =SUMIFS(B1:B3,A1:A3, ">=1/11/2015",A1:A3, "<=01/20/2015")

      Trimester 3:
      =SUMIFS(B1:B3,A1:A3, ">=1/21/2015",A1:A3, "<=01/31/2015")

  9. I NEED HELP WITH A FORMULA.
    I HAVE A SPACE THAT IS FILLED WITH AN AMOUNT.
    BELOW I HAVE ANOTHER SPACE THAT HAS THE TOTAL.
    INBETWEEN THOSE SPACES I NEED TO BUT A SQUARE WITH THE FORMULA THAT HELP ME ADD A CERTAIN NUMBER IF THE TOP SPACE REACHES TO OR OVER THAT CERTAIN NUMBER.
    I.E

    SQUARE ONE-5.00
    SQUARE TWO- FORMULA THAT READ: IF SQUARE ONE REACHES 5.00 OR OVER, THEN .50 CENTS IS ADDED ON.
    SQUARE THREE- WOULD READ THE AMOUNT BELOW 5.00 OR THE AMOUNT ABOVE 5.OO PLUS EXTRA 50. CENTS

    THANKS FOR THE HELP

    1. Hello Michael,

      You can use IF function to add a certain amount by your condition, e.g.:
      =IF(C5>=5,C5+0.5,C5)
      Here cell C5 is the amount you want to check. If it is more or equal to 5, the formula will show the value from cell C5 plus 0.50, otherwise it will show just the value from cell C5.
      As for square three, could you please describe the condition and the expected result in more detail?

  10. IN COLUMN J SOME CELL I ALREADY ADDED LIKE J54=SUM(J1:J53)AND J100=SUM(J60:J99)AND J130=SUM(J105:J129).LIKE THIS I ADDED IN K,M,O,Q.....ETC COLUMNS. NOW I ADDED THESE (J54,J100&J130) IN B350=J54+J100+J130.HOW TO PUT THE FORMULA FOR ADDING K,M,O,Q..ETC IN B351,B352,B353,B354..ETC

  11. Thanks for time range formula! It worked.

  12. I want to add c2 and d2 together with the total in e2. However i only want them to add if neither of the numbers entered in c2 or d2 are zero.

    1. Hello Jackie,

      You can use the following formula is cell E2:
      =IF(AND(C2<>0, D2<>0), C2+D2, "")

  13. I have fruits list in column A1 to A3,A1=mango,A2=apple and A3=banana.How we type by formula their price in column B1 to B3. mango=200,aplle=150 and banana=100. if i have the very lagre list.for example A1 to A100 of different types of fruits and their different amount by putting any formula and easy formula.all columns from B1 to B100 fill spontaneously.
    please reply

    1. Hello Faisal,

      Could you please specify if you want to fill column B with random numbers, or if you have another range to take the prices that correspond to the fruit name?

      If you want to fill the cells with random numbers within a certain range, you can use our Random Generator add-in:
      https://www.ablebits.com/excel-random-generator/index.php

      If you have a range with the prices, please specify where it is, or send a sample spreadsheet to support@ablebits.com. If you send your Excel file to us, please add a note with the name of the article and the number of your comment.

  14. Hi, great blog.

    I have a challenging problem:

    I have data in a large, changing table, with I need to get a percentage readiness for:

    e.g.

    Resource; A; B; C; D; E; F; etc
    Minimum Number needed; 1; 3; 2; 5; 8; 2; etc
    Team 1; 1; 2; 2; 8; 8; 8; etc
    Team 2; 3; 3; 1; 4; 8; 8; etc
    Team 3;
    Team 4 etc

    I am trying to find out for each team what the percentage readiness is, i.e.
    Team 1, Resource A shows they have one out of a required one, Resource B two out of three; Resource C two out of two. Resource E eight out of five (so more than they require) etc.
    The maximum number to use should be the Resource – i.e. Team 1, Resource A = 1/1 = 100%, B = 2/3 = 66%, C = 2/2 = 100%, D = 8/5 = 100% etc. In Total, 20 out of 21.

    I am trying to do this without a “helper table”.

    Can you help?
    Hi, great blog.

    I have a challan

    1. Hi Ben,

      Thank you very much for your feedback.

      You need to set the cell format to percentage first, and then use the following kind of formula in the table cells:
      =IF(B4/B3>1,1,B4/B3)
      Here B4 is the cell with the actual number Team 1 has, B3 is the Minimum number required under Resource A. The same for Resource B would be:
      =IF(C4/C3>1,1,C4/C3)
      Just enter the formula and copy it across the table to adjust cell references.

  15. I am trying to get a formula to work that says if the account number, and the stock code match, then pull data in from another tab. I had it working on an old sheet, but I can't find it.. I've been playing around with it for a while.

    Basically I have this on Sheet1:

    Account Number
    ABC

    Qty Stock Code Price
    123 $1
    456 $2

    and this on Sheet 2:
    Account Number Stock Code Quantity
    ABC 123 750
    ABC 456 120

    and I'm trying to get it to say for account number ABC, there is 750 of stock code 123, then stock code 456 there is 120.

    I have got data coming from all over the place, but this one is just not working. I think I have been looking at it too long :( could you please help?

    Thanks so much!

    1. Hello Bella,

      There are three ways you can do this.
      1) You can use the VLOOKUP function:
      - Create a helper column in Sheet 2 that will concatenate the Account number and Stock code, e.g. =A2&" "&B2
      This way you will get a lookup value of this kind: "ABC 123"
      - Enter a formula with the VLOOKUP function to the column with Qty in Sheet1:
      =VLOOKUP(A2&" "&C2,Sheet2!C2:D35,2,FALSE)
      It looks for the concatenated "Account number" and "Stock code" from Sheet1 in Sheet2, and returns the value from column D, which is the second from the left.
      You can find a detailed description of using multiple cells as lookup criteria in this blog post.

      2) You can use the combination of INDEX and MATCH functions,
      - Create a helper column in Sheet 2 that will concatenate the Account number and Stock code, e.g. =A2&" "&B2
      - Enter the following function to get Qty from Sheet2:
      =INDEX(Sheet2!D1:D35,(MATCH(Sheet1!A2&" "&C2,Sheet2!C1:C35)))

      Please see this blog post for more details.

      3) You can use our Merge Tables Wizard add-in:
      - Select Sheet 1 on step 1
      - Select Sheet 2 on step 2
      - Select "Account number" and "Stock code" as key columns to compare
      - Choose "Qty" as the column to update on step 4
      - Pick any additional settings you like on the last step and click Finish.

  16. Wonderful blog!

    The first example is what I need help with - SUMIF(range, criteria, [sum_range]. I need to do exactly as described, for a report on Sheet 3. Here is the challenge:

    Sheet 1 has the range and the [sum_range] , but the text criteria (also on sheet 1) is a drop down list with the source list on Sheet 2.

    How do I reference the text (for report on sheet 3) in the drop down box (sheet 1) as the criteria?

    Thank you!

    1. Hello Sandy,

      Thank you very much for your feedback!

      If you want to refer to the cell with a drop-down list as to changing criteria, you can enter its address just like you would enter any other cell address.

      If you want to create a drop-down list for the report in Sheet 3 and see the results of the SUMIF function depending on the selected value, you can add the same data validation list on Sheet 3 and refer to it in your formula, e.g.:

      =SUMIF(Sheet1!D1:D6,D1,Sheet1!C1:C6)

      Here D1 is the cell in Sheet 3 that contains the drop-down list. When you change the value in it, the results will be updated accordingly.

  17. I am trying to return a value when I have different time frames in column c (24 hour period - different minutes every time, example: 0415, 0423, 0439, 0501 military time, each time frame has a number of passengers arriving on that hour. I tried using =SUMIF(C2:C12,"04",B2:B12)to return how many passengers are arriving between 0400 hours and 0459 hours, but all that the formula returns is the sum of the first 0400 line (10), instead of returning the sum of all flights that start with "04". Can you help? Thanks.

    1. Hello Charlie,

      The SUMIFS function works a treat, the formula goes as follows:
      =SUMIFS(B2:B12,C2:C12,">="&TIMEVALUE("04:00"), C2:C12,"<"&TIMEVALUE("05:00"))

      This function sums B2:B12 for the time interval 4:00:00 to 4:59:59. Naturally, you can specify other time intervals in the formula if needed.

      For more information about the SUMIFS function, please see:
      https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/

  18. Spent 30 minutes trying to display a a sum of a range when another cell expressed a specific word. You solved it.
    Formula: =SUMIF(C1:C168,"AnPac Insurance",D1:D168)
    Thank you

  19. I'm trying to create a formula that would add together the tax free price of my sales and the VAT to total my income.

    So far the Formula looks like =SUMIF(A5;"x";E5:F5)

    On the A column I will mark an "x" to indicate that the bill has been paid. Colum E has the tax free price and column F has the VAT.

    The formula will not add the two together but give me the value of Column E as the total sum (i.e. missing the value from column F).

    What am I doing wrong?

    1. Hello Antti,

      I am sorry, it is difficult to recommend you anything without seeing your data. Please post this question on our forum with a sample workbook attached and our support team will try to help.

  20. I have a table containing data (a number of transaction Column A1 date showing i.e., Wednesday, November 11, 2014. and in column B Sales amount) now from that table i want to sum all the sales occured in Wednesday. is it possible?

    1. Hi Obaydul,

      Yes, it is possible. You can use the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM( (--WEEKDAY($A$2:$A$100)=4)*($B$2:$B$100))

      The WEEKDAY function takes Sunday as 1 by default, so 4 stands for Wednesday.

      1. In Excel sheet we have a marksheet having 10 students. Column headings are Name, Hindi , Eng, Maths and Total.
        Using Sum Function we have find out Total marks obtained. Next we have found out Maximum marks using MAX function. Is it possible in Execl to print Name of student who has secued Maximum marks along with maximum numbers. i.e. Max marks along with candidate name

  21. I am trying to create a formula across rows for a work holiday time off calendar. I want to create for each person that it reads each month across the rows that if they are on holiday, we can put a H on those dates and the formula will read this and take the total number of holiday days down.
    I can do this when the month is in columns but not sure how to get the formula to work if it is in rows.
    Hope this makes sense?

    1. Hello Clare,

      You can use the following kind of formula to count the number of cells that contain H:
      =COUNTIF(B1:B31,"=H")

      The formula above will check cells 1 to 31 in column B; you can change the range to row or column depending on how your data are organized.

  22. Sorry that should look like this

    sheet one
    Fruit No Boxes Colour supplier
    apple 2 green jones
    pear
    orange
    apple 4 red smith
    lemon

    sheet two
    apple 2 green jones
    apple 4 red smith

    1. Hello John,

      Could you please specify what you would like to get as a result? If you want to pull information based on the same value in column A, e.g. "apples", then the VLOOKUP function or our Merge Tables Wizard add-in will help you.

      If you want to find repeating values in several columns, please see this article.

      If your task is different, you can send a sample spreadsheet with the way you have your data and the way you want to see it in the result to support@ablebits.com. Please include the name of the article and your comment number. We'll do our best to assist you.

  23. Hi

    I have an excel workbook with several sheets what i like to do is display information from one sheet onto another
    IE if the word apples is displayed a column i want the information displayed in the row containing the word apple

    Sheet one sheet Two
    apple boxes, 2 type, green supplier, jones apple boxes 2 type green supplier jones
    pear apple boxes ,4 type, red supplier, smith
    orange
    apple boxes ,4 type, red supplier, smith
    lemon

    Many thanks John

  24. I am facing a difficulty where I have many data in different rows and columns,but I cant seemed to have this function valid for it. Can you please suggest a formula or point me in the right direction? Thank you!!

    E.g: Data which i need to lookup to is from Jan - Dec. But i have 3 different data sets to lookup from , which is actual numbers, historical numbers and budgeted numbers for each single month, on my left column. Apart from that, on my left column, i do have PLANT A, PLANT B, PLANT C which has that 3 actual/historical/budgeted numbers.

    1. Hi George,

      Sorry, it's a bit hard for me to understand the structure of your data. If you can post a data sample with column letters and specify what exactly result a formula should return, we'll try to help.

  25. I am trying to create a formula where there is always a fixed price ($70). Every bathroom is $10. Every square foot over 700 is $0.10. I've been trying to create a formula that adds the fixed price, price per bathroom, and price for square foot over 700, but I can't figure it out. Any feedback would be great!

    1. Hi Caroline,

      If you can tell me a bit more about your data structure, I'll try to work out a proper formula for you. In particular, in what columns do you have all those prices and item descriptions?

      If you can post a sample of your data and the expected result, this would be even more helpful.

  26. This is a really helpful way for students to have free time and study propular on the questions that is serious for them to know about.

  27. In a certain column which records date,let few cells have left empty(without any date).
    so, in that case what to do if we want to autofilled them "no date given" in those cells.In what way we sud use conditional stat.

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