Comments on: How to use Excel SUMIFS and SUMIF with multiple criteria

This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel. Continue reading →

Comments page 4. Total comments: 675

  1. Hi Alexander,

    I am trying to figure out how to calculate Monthly recurring revenues when new customers coming in and others leave.

    Assume all customers remain only 4 months

    Month 1 2 3 4 5 6
    New monthly revenues 100 300 500 700 900 1100
    Monthly recurring revenues 100 400 900 1,600 2,500 ?????

    How do I make sure that in month 6 I do the following?

    Sum all New monthly revenues from months 1 through 6 EXCEPT months 1 and 2 because the time that has passed by is greater than 4 months?

    Thank you
    -

    1. Hello!
      If you need to use a formula in the summation condition, then use the SUMPRODUCT function to calculate the sum by multiple criteria:

      =SUMPRODUCT(--(A1:L1>(MONTH(TODAY())-4)),--(A1:L1<=(MONTH(TODAY()))),A2:L2)

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

      1. Dear Alexander,

        1. You are my hero
        2. I am forever grateful
        3. Your help will make me look good in front of my colleagues!

        Thank you!
        Carlos

  2. Hello, Sir kindly guide me what's the best platform for advanced excel learning and free of cost.

  3. I used below formula and total amount = 70. Seems it only adding "Terry". I want to see the total amount of Terry, John and Pete. Am I missing something? Thanks.

    =SUM(SUMIFS(B2:B10,A2:A10,{"Terry", "John", "Pete"}))
    name amount
    Terry $10
    John $20
    Terry $30
    Pete $10
    John $30
    Lian $20
    George $10
    Pete $20
    Terry $30

    1. Hi!
      Unfortunately, I could not get the result 70. Your formula gives a result - 150. Try to enter your formula as an array formula. Enter via Ctrl + Shift + Enter, not just Enter.

  4. Hi

    May i request anyone, how can i use cell reference in sum if instead of typing manually.

    Example-: SUMIF('Jan22-29.3.22'!$AL:$AL,"*WTFL*",'Jan22-29.3.22'!$AQ:$AQ)

    Question: Why need to edit the formula to type WTFL or any other word in order to get total.

    Thanks
    Anoop

    1. Hello!
      Replace the value in the formula with a reference to the cell that contains that value.

      SUMIF(‘Jan22-29.3.22′!$AL:$AL,$A$1,’Jan22-29.3.22’!$AQ:$AQ)

      A1 -- *WTFL*

  5. i was using sumifs formula with multiple criterias. but i am unable to fetch the previous criteria data if some criteria are blank. need your support

  6. Trying calculate the total revenue (in cells T3:T11000) IF the service type = Social (in cells D3:D11000) AND the project status = Active (in cells K3:K11000) AND the invoice date falls in between 01/01/2021 and 01/31/2021. Report totals to appear on a separate tab from the summary data on tab entitled "All Projects".

    My (erroneous) attempt:
    =SUMIFS('All Projects '!T3:T11000,'All Projects '!D3:D11000,"=*SOCIAL*",'All Projects '!C3:C11000,">=01/01/2021",'All Projects '!C3:C11000,"<=01/31/2021")+SUMIFS('All Projects '!T3:T11000,'All Projects '!K3:K11000,"*active*")

    Can you please help? Thank You!

    1. Hi!
      Please re-check the article above since it covers your case.
      Here is a sample formula for you

      =SUMIFS(T3:T7,C3:C7,">01/01/2021",C3:C7,"<31/01/2021",D3:D7,"*Social*",K3:K7,"*Active*")

  7. Dear Sir, i want to apply this condition can you help me regarding this

    Excess [if (C7 = 1+1+1,3*D7*G7),(if (C7 = 1+1,2*D7*G7),if (C7 = 1,1*D7*G7),0

  8. Dear i want to apply this condition can you help me regarding this

    Excess Un-adjusted Credit [if F24 = Yes and F25 > F17 then (F25 - F17); otherwise zero; if F24 = No then (F8 - F25)]
    her "F" is column and numbers are rows

  9. Hi, I want to type Not Available or text when the result of Sumif or Sumifs = 0 (Zero) Please advise

    1. Hello!
      Use IF function to get the result by condition. For example:

      =IF(SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")=0,"Not Available",SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete"))

  10. Long time follower....first time poster ;)

    Trying to set something up where I can get my MTD target based on day of month input.

    Example: If in Cell A1 I input '7' for the 7th day of Jan.

    In say cell B9, I want it to reflect the sum of daily targets in a table on a separate sheet.

    For simplicity sake: the table is C7:AG7, I would want B9 to be the SUM of C7 to I7.

    I can't figure this one out

  11. Hi. Please I’m trying to use this formula buts it’s only recognizing the security. It’s not summing up the security and electricity.

    =SUMIFS('259222496_1_59_0'!D12:D,'259222496_1_59_0'!I12:I,{"O.N- SECURITY","O.N- ELECTRICITY"},'259222496_1_59_0'!A12:A,">="&DATE(YEAR(B5),MONTH(B5),1),'259222496_1_59_0'!A12:A,"<="&EOMONTH(B5,0))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Explain what data you are using and what result you want to get.
      Also, notice the paragraph above: Using Excel SUMIF with multiple OR criteria.
      {“O.N- SECURITY”,”O.N- ELECTRICITY”} - this does not work.

      1. I want to sum up the figures with “O.N Security” and “O.N Electricity”. I haven’t able to do them individually with the formula but I’m trying to sum them together

        1. Hi!
          Read the paragraph I recommended. There is an answer to your question. I cannot write you a formula, as it contains unique references to your data, which I do not have.

  12. Quote value Days_to_close Salesman Quote_month
    234 90 Ram 01-Jan-15
    342 100 Satish 05-Feb-15
    122 34 Suresh 15-Apr-15
    544 67 Suresh 15-Nov-15
    232 99 Ramu 14-Nov-15
    533 89 Babu 29-Dec-15
    21 63 Suresh 01-Dec-15

    SUM of Quote_Value if Salesman name is Suresh and Days_To_Close is equal to or less than 90 and Quote_Month is equal to one of the following (Oct-15, Nov-15, or Dec-15).(using formulae)

    1. Hi!
      Have you tried the ways described in this blog post? Please re-check the article above since it covers your case.

  13. Hi,

    I have jam and other stuff everywhere.
    I want to know how much jam is in the larder.
    So, trying to use SUMIFS, I have the following spreadsheet.

    My Σrange is all the numbers; My first criterion range is all the stuff; My first criterion is stuff: My second criterion range is all the places; My second criterion is places.
    It doesn’t work. Whyyyyyyy?

    In reality, I’ve a Census database of 'How many speak each of 200 languages in each of UK’s 1000 regions'.
    Finding how many speak something somewhere manually is a pain in the archive.

    (To simplify entering the two criteria, I’ve used ‘data validation’, with an input-list for each criterion.)

    Here's the spreadsheet - or it would be if it could be copied and pasted properly!

    criterion 2 Larder
    criterion 1 jam #VALUE! ←←

    places → Larder Cupboard Fridge
    stuff ↓ biscuits 10 20 3 ← numbers
    butter 3 0 1
    cheese 4 0 2
    jam 15 5 0

    ……....................................................................................
    Formula (in F16): SUMIFS (F19:H22, E19:E22, E14,F18:H18, F13)
    [the spaces are for easier reading only]
    --- In 'English': Sums if ( Σrange, C1range, C1, C2range, C2)

    1. Hello!
      All ranges of values in the SUMIFS function must be the same size. You can read about this in the article above. An example of your formula:

      =SUMIFS(E19:G22, I19:K22, E14, M19:O22, F13)

  14. I want a conditional formula for insurance calculation based on below criteria

    Gender Name Age Married/Single Insurance Calculation
    Male XYZ 16 Single
    Male XYZ 23 Single
    Male XYZ 58 Married
    Female XYZ 60 Married
    Female XYZ 25 Single
    Male XYZ 29 Married
    Female XYZ 32 Married

    Criteria:

    Gender/Status Amount Age
    Male/single 265.00 From 5 to 19 years
    Male/single 270.00 From 20 to 40 Years
    Male/single 300.00 from 41 to ETC
    Female/Single 270.00 From 5 to 19 years
    Female/Single 300.00 From 20 to 40 Years
    Female/Single 350.00 from 41 to ETC
    Male/Married 300.00 From 5 to 19 years
    Male/Married 350.00 From 20 to 40 Years
    Male/Married 400.00 from 41 to ETC
    Female/Married 337.00 From 5 to 19 years
    Female/Married 388.00 From 20 to 40 Years
    Female/Married 442.00 from 41 to ETC
    Female/Maternity 450.00
    Child 200.00

  15. Thanks a lot,
    it's worked..

  16. Hi, I hope you could help.

    I have a table where I need a sum to be displaying in the same cell, however it will vary but based on two options:

    Sum for option 1 multiplied by the quantity
    OR
    Sum for option 2 multiplied by the quantity

    Thank you in advance!

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice.
      Please describe all the conditions of the formula so that I can help you.

  17. Hi there

    I want my cell to look for a set name (construction ref. A7) in another sheet and if that is true display the ammount of money spent so far but ONLY if its a positive number

    Current construction has a loss of £500

    I would like the loss to be shown in a second column by repeating the fomula but with 0)

    but this is doing the maths of 900 minus -500 giving a return of 400

    I dont know if i need to be combing VLOOKUP or if it should be a count etc? im very lost

    1. sorry the fomula got lost there

      =SUMIF(Arnold_Sector,A7,Arnold_outcome)*(Arnold_outcome>0)

      1. Hello!
        Based on your description, it is hard to completely understand your task.
        To display only a positive amount, use the formula:

        =IF(SUMIF(B2:B9,A7,C2:C9)>0,SUMIF(B2:B9,A7,C2:C9),"")

        To calculate the sum of only positive values by condition, use the formula:

        =SUM((B2:B9=A7)*(C2:C9)*(C2:C9>0))

        1. Well i want it to comeplete the first task of looking to see if A7 is in the column, then look a the second column for the ammount related to it and add them up if theyre postive numbers

          For example, i have got it to count the ammount of Constructions (2) and the total spent 1400 and i want it to look at the individual spends as a positive or negative and only add them up if theyre a positive (i can repeat the task for negative in a seperate cell)

          thankyou for your help on this

  18. Trying to figure out how to sum a row with date columns in it.
    I want it to ignore negative numbers and the date.
    Such as the following
    "Buyer" "Amount" "Jan Date" "Amount" "Feb. Date" "Amount" "Mar. Date"............"Total Due"
    Fred $1000 1/1/21 -$300 2/10/21 $500 3/20/21
    Sally $300 1/20/21 -$100 3/10/21

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

      =SUM((A2:A5="Fred")*(B2:B5>0)*B2:B5,(A2:A5="Fred")*(D2:D5>0)*D2:D5)

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

  19. Is there an easier way to type this sort of formula?

    =SUMIFS(Sheet1!D:D;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!E:E;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!F:F;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!G:G;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!H:H;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)

    ...and goes on like this for a few more columns. Using Excel 2016.
    Thanks

    1. Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Explain what you want to do.

  20. Good evening sir, i have some questions as below:
    1. Why need toput sum infront of sumif/sumifs? Any the result place sum(sumif/sumifs) is correct?
    2. is there any easy way to get value number from the value text? Exp: i wanna get 100 in number from the text $100.00?
    thanks

  21. I have created a weekly revenue record of a hospital spread over a month. I created it such that each sheet contains 3 month record by-weekly. Now my sheet 2 is supposed to be a summary sheet categorizing the income into the various hospital unit . I have used the SUMIFS Formula, it worked but could not place the revenue in its appropriate category.e.g revenue from pharmacy should go to pharmacy in the summary sheet while Revenue from laboratory should go to laboratory.but it is not so with what I created.I believe something is not right with my formula. What could be the problem and please can you help me out?
    Thanks

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. I recommend paying attention to the SUMIFS function. You can find the examples and detailed instructions in the article above.

  22. hai thanks for this page, i can build 2 criteria and with 3 criteria.

    with 2 criteria
    =SUMIFS($G$18:$G$501;$D$18:$D$501;"Apple";$F$18:$F$501;"23/06/2021")

    with 3 criteria
    =SUMIFS($G$18:$G$501;$D$18:$D$501;"Apple";$F$18:$F$501;"23/06/2021";$B$18:$B$501;"city")

  23. in sumif how we can get average no.

  24. Thank you for these great explanations and clear examples. They helped me automate some manual things I have done over the past few years, and will help me reduce errors. Thanks!

  25. Thank you for the formula and explanation of SUMIF(range;{array};range) to create an array of results and SUM(SUMIF(range;{array};range)) to sum the array of results into one cell! It helped me very much.

  26. Hi, The formula that I am using is =SUMIF('FA Journal'!A1:A10000,L51,'FA Journal'!J6:J615). I need this formula for over 5000 rows when I drag the formula down, the range (A1:10000 becomes A2:A10001, I want criteria to keep change as per the row number but not the range. How can I resolve this issue? Thanks

    1. You can use A:A for range, which helps a bunch while using sumif and suimfs functions

  27. I have a question about sumifs with multiple criteria. There is an example that shows products (orange, apple, banana), suppliers (Pete, Mike, ) and qty. I apply the similar logic/formula to a spreadsheet with a slightly different layout and it returns an error (#value). Imagine a spreadsheet where the name of the suppliers (Pete, Mike, etc.) are in row 1 and repeated across several columns multiple times. Now imagine in column A are the names of products (orange, banana, etc.) and they also appear in several rows multiple times. Inside the spreadsheet, where supplier/product intersects are the numbers (Qty). When I apply the same formula/logic), it returns error. My table has 10 columns (B to K) and 10 rows (A2 to A11). Here's my formula: =SUMIFS(B2:K11,B1:K1,"Pete",A2:A11,"apple"). This gives an error message (#value). Why wouldn't it count/add the quantities in the cells based on the criteria in this type of spreadsheet? I think it has something do with the table layout but can't figure it out.

    1. Hello!
      The SUMIFS function works with conditions in only one column, not a range of data. Therefore, in your case, this function cannot be used.
      Try this formula:

      =SUMPRODUCT(B2:M9*(A2:A9=N1)*(B1:M1=P1))
      N1 - product (apple)
      P1 - supplier (Pete)

      You can change it for your data.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hi Alexander, thank you but it is not working. I believe B2:M9 is the array (range) for the table/area that contains the values so that's what I selected. N1, P1, I used the values as +"apple", "pete" or was I supposed to create a separate tables to define P1 and N1? In any case, the formula resulted in error. Maybe you can test it at your end and send me an updated formula.

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. B2:M9 is the range that numbers (Qty) are in. N1 says "apple", P1 says "Pete". Column A says orange, apple, banana, apple, banana, etc. Row 1 says Pete, Mike, Pete, etc.
          What doesn't work for you?

  28. how to use for sumifs function for two tables example i have table 1 and table 2 have same structure with different data

    TABLE 1 TABLE 2
    TEAM 1 Credit TEAM 2 Credit
    Cameron Bancroft 8.5 Josh Philippe(w) 8.5
    Liam Livingstone 9 James Vince 9
    Mitchell Marsh 10 Daniel Hughes 10
    Colin Munro 8 Moises Henriques(c) 8
    Josh Inglis(w) 9 Jordan Silk 9
    Ashton Turner(c) 9 Daniel Christian 9
    Aaron Hardie 9 Carlos Brathwaite 9
    Jhye Richardson 10 Sean Abbott 10
    Andrew Tye 8 Ben Dwarshuis 8
    Jason Behrendorff 8 Steve OKeefe 8
    Fawad Ahmed 10 Jackson Bird 10

    i need output

    OUTPUT TO BE LIKE THIS
    PLAYING TEAM Credit
    Daniel Hughes 10
    Moises Henriques(c) 8
    Jordan Silk 9
    Cameron Bancroft 8.5
    Liam Livingstone 9
    Mitchell Marsh 10
    Colin Munro 8
    Josh Inglis(w) 9
    Fawad Ahmed 10

  29. Hi Alexander,

    I need your help with summarizing my data.
    I have data about 162 countries from 1970 to 2013. In each year, I have data about the frequency of s specific ethnic group. There are different ethnic groups for each country, and I'm trying to summarize for each country the data of all the frequencies in a given decade. I can't just roll the command of "sum" to all countries because they are all different in the number of ethnic group cells.
    What can I do?

    It looks like this:
    country year ethnic group freq SUM
    US 1970 muslim number SUMMARY for 1970
    US 1970 jewish number
    US 1970 romancatholic number
    US 1970 non.reli number
    US 1970 orthodox number
    Canada 1970 hindu number
    Canada 1970 sikh number
    Canada 1970 protestant number
    Canada 1970 buddhist number
    Canada 1970 jewish number
    Canada 1970 Muslim number
    Canada 1970 non.reli number

    1. Hello!
      Here is the article that may be helpful to you: Vlookup multiple matches in Excel with one or more criteria.
      I hope this article will be helpful. Unfortunately, you did not explain exactly which data you want to summarize. Your task is not very clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  30. great blog post

  31. Hi Alexander,

    I need help with a 2D sumifs function... My firm has had employees tract their time details in a single spreadsheet.... Column A is the start time (standard dates in 6 minute increments starting from "8:00:00 AM"), column B is the stop time, column c is the project on 1/1, column d is comments on 1/1, column e is the project on 2/2, column f is 2/2 comments etc.

    I would like to write a single formula in a summary sheet where I can reference each employees timesheet, and list each project in column a and each date in row 1, and summarize the data in the resulting table. his would be a simple copy and paste formula if the comments weren't there, but i don't want to delete all those extra columns for every employee. I have seen articles with a 2D sumif, but the index(match() is always used on the sum and never on the criteria.

    Thanks.

  32. how can apply and (&) statement ( to sum different cells with multiple condition in SUMIFS function

    1. Hi,
      The syntax for the SUMIF function does not include &. If you explain your problem in more detail, I will try to help you.

  33. Hello,
    I have multiple columns I need to be able to sort Month by Month and then I have 9 columns that I need to find a % of based off the date or the month.
    Ex: DOB, Male, Female, in a relationship, single, bald, has hair, Left handed, right handed. What % of males are single, bald and right handed.
    Any help would be greatly appreciated!

  34. Hello there,

    =SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)

    this sums regardless of any product, what if i want it to sum based on the product without declaring it in the formula

    Ex: get me the sum of Mike and Pete if the prodcuts were a match
    it gets me the oranges sum alone and the apples alone without me specifying it in the formula

    could you help please?

    1. Hello!
      I recommend using the SUMIFS function, which calculates the amount according to several conditions. Please read the above article carefully.

  35. I want to add sumifs 12 times( for calculation for 12 months). 3 additions are resulting correctly after that results are not as desired

    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

  36. I want to add cell starting with 2 common letters. How can this be added to the formula.
    Like
    FL20.3 -- 40
    FL30.1 --100
    BL12.4 -- 10
    TC10.001 -- 5
    I want to add all FL20.3 and FL30.1. Is it possible?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!

  37. I am trying to create a SUMIF formula for a variety of fields.
    This is my current formula
    =SUMIFS('General Acct - 2020 Debits & Credits'!$G$581:$G$648,'General Acct - 2020 Debits & Credits'!$B$581:$B$648,{"Transfer to Secured Acct (Special Projects)","Transfer to Reserve Acct","Transfer to Hydro Acct"},'General Acct - 2020 Debits & Credits'!$C$581:$C$648,"Other")

    The just of it is that if any of the "Transfer Accts" are chosen in column B and "Other" is chosen in Column C then then sum all amounts associated in column G (debit column)

    right now if just returns 1 individual amount

    1. Hello Erika!
      If I understand your task correctly, you can use the SUM function:

      =SUM(SUMIFS(‘General Acct – 2020 Debits & Credits’!$G$581:$G$648,’General Acct – 2020 Debits & Credits’!$B$581:$B$648,{“Transfer to Secured Acct (Special Projects)”,”Transfer to Reserve Acct”,”Transfer to Hydro Acct”},’General Acct – 2020 Debits & Credits’!$C$581:$C$648,”Other”))

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

  38. hi , iam traying to sum the time for user level in multiform time , date and user id
    below is the example
    PTW_USER_ID PTW_DATE PTW_TIME
    58169 24-Jul-20 7:25:51
    155129 24-Jul-20 7:28:45
    58169 24-Jul-20 7:16:08
    30124 24-Jul-20 7:18:04
    30124 24-Jul-20 7:47:46
    58169 24-Jul-20 7:12:10
    111885 24-Jul-20 6:20:19
    111885 25-Jul-20 0:05:55
    111885 25-Jul-20 0:07:16
    111730 25-Jul-20 0:44:54
    111885 25-Jul-20 0:05:55
    111885 25-Jul-20 0:07:16
    111730 25-Jul-20 0:44:54
    111020 26-Jul-20 0:01:57
    111885 26-Jul-20 0:01:05
    111020 26-Jul-20 0:28:10

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?

  39. I'm trying to come up with a way to calculate total time, by code, by date.
    Example: how much time was coded, on the 21st for the K90 code?
    Date Code Time
    7/19/2020 NU0 4.18
    7/21/2020 K90 5.33
    7/21/2020 K90 0.5
    7/21/2020 E42 2.67
    7/20/2020 E42 4.92
    7/20/2020 K90 8.48
    Thank you for any help

    1. Hello Mike!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =SUMIFS(D50:D55,B50:B55,DATE(2020,7,21),C50:C55,"K90")

      I hope it’ll be helpful.

  40. As a general comment on SUMIFS; note that if none of the criteria are met, the result is zero, not an error of some kind.

  41. I have received 25 items and want to deliver 5 person. Can you please help to set the formula.

  42. sir,
    I am using sumif formula to sum 3 columns data, but it giving 1column summary.
    syntax is:SUMIF(G3:G619,H621,C3:E619).
    pl advice , how to correct it.

  43. Hello, Please help and thank you in advance!!

    I have a formula to sum values "TimeSheet!E:E" according to month selected "AC2" and by ID & Emp, but what I have been struggling with is the other part of the formula if a month is not selected and a quarter is selected.
    Instead of months as text, I use 1-12 to simplify formula. This is the formula that I am using.

    =SUM((MONTH(TimeSheet!$G:$G)=$AC$2)*(TimeSheet!$B:$B=$B5)*(TimeSheet!$J:$J=E$2)**(TimeSheet!$E:$E))

    AC2 (Month 1-12/1Qtr, 2Qtr, 3Qtr, 4Qtr) B:B (Emp Column) E:E (Miles Column) G:G (Dates Column) J:J (ID Column)

    The formula I have been trying is:

    SUM(SUMIF(AC2,{"1","2","3","4","5","6","7","8","9","10","11","12"},TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,(MONTH(TimeSheet!$G:$G)=$AC$2,TimeSheet!$E:$E),SUMPRODUCT((ROUNDUP(MONTH(TimeSheet!$G:$G)/3,0)=AC2,YEAR(TimeSheet!$G:$G)=AC2,TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,TimeSheet!$E:$E))

    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. What exactly do you indicate in cell AC2 (month number or quarter number or date)? What data is stored in column G (date or month number)? Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  44. How do I calculate the total sum in a column which is attached too separate conditions in two different columns?

    Eg: Sum column D if any of the column A (is>700 and 700 and <1400).

    Which formula will work for this scenario?

    1. Hello Akash!
      Read carefully the article above. Use a formula like this

      =SUMIFS(D1:D10,A1:A10,">700")

      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  45. Hi
    Please help me with this table
    I have this table. A,B,C are the excel columns and 1,2 3 .... are the rows.

    A B C
    1 Daily Price update
    2 Apple 2.6
    3 Banana 10
    4
    5 Type of fruit Last days price Today’s price
    6 10

    My requirement is that when I make an entry in A6 (type of fruit), it should check value from A2 or A3
    Then it should add value of B6 as per values given in B2 or B3 as per entered value.

  46. HOW TO AVOID TOTALS TO CHANGE IN SUMIFS FORMULAS WHEN YOU FILTER THE DATA

  47. SUMIF and SUMIFS can handle ranges, but not arrays. This means you can't use other functions like YEAR on the criteria range, since the result is an array. If you need this functionality, use the SUMPRODUCT function .

  48. amount days value
    100000 215 1767 (formala =a*d*3/36500)
    i want calculate amount 100000 calculate only one lack

  49. Hi there
    I'm trying to sum info in a slightly different format. If in your first example if the supplier names (John, Mike,Pete) were in cells B1-D1 and all the sales were in B2-D9 under their names, I'm wanting to get the total number of fruit from each supplier on a separate sheet. ie Sumif John and Apples.
    It should be an easy solution, but I'm not getting it - can you please assist?

  50. Hello,

    I am trying to do a sum function in for the number of rows depending on the number of month we are in. For example columns C through N are for the months Jan - Dec. On a separate Cell number 5 for "May". I want to be able to add columns C through G for Jan - May. Can this be done?

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