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

  1. Hi,

    i have SAP product codes which are 19 in digits which are note reading by "SUMIFS" while considering multiple conditions like "900000002831022000".

    If I'm decreasing its digits lesser than 19, its working perfectly.

    Could you please assist on it.

      1. Much appreciated.

  2. Hi I have 2 dynamic range.
    I used sumifs(Sales, Criteria1 Range , Criteria 1,Criteria2 Range , Criteria 2)
    Criteria 1 (has up to 4 options) and 2 (up to 3 options) are both dynamic, meaning i have created a slicer which will change accordingly to the options selected. I have defined Criteria 1 and 2 range under Name Manager. It works when having 1 Criteria but doesnt work if i include both.

    May you advise pls
    Thanks

  3. Hello,
    What if I want to count the bananas from both suppliers John and Mike? Do you have a better solution than :
    =SUMIFS(C2:C9, A2:A9, "Bananas", B2:B9, "John") + SUMIFS(C2:C9, A2:A9, "Bananas", B2:B9, "Mike") ?
    How can I put option A or Option B or Option C in the same criteria in that case that would apply to B2:B9?
    Thank you!

    1. Hi! If you read carefully, the last paragraph of this article has the answer to your question. Here is an example of the formula:

      =SUM((A2:A10="Bananas")*((B2:B10="John")+(B2:B10="Mike"))*C2:C10)

  4. HI, I want to use SUMIFS, and the cells in the range that I want to consider already have a formula (IF), but the SUMIFS is not considering those values. How can I do? thanks!

    1. Hi! The SUMIFS function does an excellent job of counting cells with formulas. What is important is what values these formulas return.

  5. Hi

    I have data with multiple units (i.e, Kg, Pcs, Nos, etc). Some items may appear in multiple units. for example, sometimes I sell an item in pcs and sometimes in Kg. So, to get how much qty I sold in a month what will be the equation?

  6. Hi, I have an issue in defining a formula to sum different rows in a matrix based on different filters:

    Date 29/12/2022 29/01/2023 29/01/2024 29/01/2025
    Flow -189.110,0 5.890,0 5.890,0 5.890,0
    Key 122022 12023 12024 12025
    Date 29/12/2022 16/09/2023 16/09/2024 16/09/2025
    Flow -175.723,4 6.317,5 6.317,5 196.317,5
    Key 122022 92023 92024 92025

    I have a matrix like this (sorry for the format): I have to define a formula to sum the "Flow" row based on the key field. Say I have to build a cash flow report, so I have all the month for a year and for each month I have to sum the different flows through the "Key" row. Is there a formula without repeating sumif for all the lines?
    Many thanks for any help.

    Alberto

    1. Hi! SUMIFS function cannot sum over columns. So try the SUMPRODUCT function. I'm not quite sure how your data is organized, but the formula might look something like this:

      =SUMPRODUCT((B3:N3=12022)*(MONTH(B1:N1)=1)*(YEAR(B1:N1)=2023)*B2:N2)

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

  7. How do I ask Excel to give a sum or count for data like the below? For the below, I would want a count of 1 because all activity occurred roughly at the same time. I don't want to count each row as its own instance but rather count them as one singular event.

    StationName TransactionDateTime TransactionType MedID DrawerSubDrawerPocket Quantity
    G3SE 1/1/2023 9:21 Refill 64573 Drw 3.14-Pkt 2 3
    G3SE 1/1/2023 9:21 Refill 64573 Drw 3.14-Pkt 1 3
    G3SE 1/1/2023 9:21 Refill 64573 Drw 3.14-Pkt 3 0
    G3SE 1/1/2023 9:22 Refill 64573 Drw 3.9-Pkt 2 1
    G3SE 1/1/2023 9:22 Refill 64573 Drw 3.9-Pkt 4 0
    G3SE 1/1/2023 9:22 Refill 64573 Drw 3.9-Pkt 1 2
    G3SE 1/1/2023 9:22 Refill 64573 Drw 3.9-Pkt 3 0

  8. Hi All,
    From the example:
    =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))

    If i want to store the set of criteria in a cell how would i do that, when swap the criteria for a cell, and paste the values into the cell it does not work, so:
    =SUM(SUMIF(C2:C9, A1, D2:D9))
    In cell A1 - {"John","Mike","Pete"}
    or "John","Mike","Pete"

    Any ideas?

    1. Hi! The SUMIF function cannot get an array of values from a cell. You can use a cell reference that contains only one value.
      For example,

      =SUM(SUMIF(C2:C9, A1, D2:D9),SUMIF(C2:C9, A2, D2:D9))
      where A1 - John. A2 - Mike

      You can use the TEXTSPLIT function which will return an array of text values from a cell using a separator. For example,

      =SUM(SUMIF(C2:C9,TEXTSPLIT(A1," "),D2:D9))
      where A1 - John Mike Pete

      1. Been Trying this out since Wednesday, and you just solved the puzzle.
        Thanks alot sir!
        Alexander Trifuntov (Ablebits Team)

  9. Hi,
    I want to sum Hours!C8:C373 (this is employee time off hours)
    If Hours!D8:D373 (this is the time off ID code)
    = Summary!D4 (this is the time off ID I'm looking for)
    And Summary!C5 (this is the employee name whose info I'm looking for)
    = Hours!C7 (employee name and column header)

    I tried this =IF(C5=Hours!C7,SUMIF(Hours!$D$8:$D$373,F$4,Hours!$C$8:$C$373),"") on the Summary sheet and it works.
    Is there a better way? Thanks

  10. IS the following formula correct:

    =SUMIFS(D60:D1549,("4" ,TEXT(D60:D1549,"mm")))
    please write the correct one.
    Thanks so much.

    1. Hi! The SUMIFS function cannot use other functions or expressions as arguments. Only values or cell references can be arguments to this function. I can't fix the formula as I don't know what you want to do.

  11. HI.

    I have something like this

    SUM(SUMIFS(Sheet1!D:D,Sheet1!A:A,"ABC",Sheet1!K:K,{"CTA","LoB"},Sheet1!T:T,{"Delivery Complete","In Progress","Backlog"})))

    My formula is not working properly.

    Any solutions to this is appreciated.

    1. Hi!
      The SUMIFS function cannot use an array as a criterion. Replace {"CTA","LoB"} and {"Delivery Complete", "In Progress", "Backlog"} with OR logic, as described in the article above.

      SUM(SUMIFS(Sheet1!D:D,Sheet1!A:A,"ABC",Sheet1!K:K,"CTA",Sheet1!T:T,"Delivery Complete"))) + SUM(SUMIFS(Sheet1!D:D,Sheet1!A:A,"ABC",Sheet1!K:K,"CTA",Sheet1!T:T,"In Progress"))) + ........

  12. Hello, Please help and thank you in advance!!
    =IF(D4:D7="KHR",E4:E7/4000,SUMIF(F4:F7,"Red",E4:E7))

    1. Hi! I don't know what you want to do, but note that D4:D7="KHR" returns an array of 4 values. Your formula cannot work.

  13. Hello, I have a data table with 3 parameters(columns for tonnage ex 1,2,..., another w two options( yes/no), and a row with categories. The matrix of the table are results of the above parameters (ex: 1,no,category-> ## or 1, yes, category-> ##(different from 1st)). I'm trying to simplify everything by having dropdown boxes for each parameter and then using sumiffs to get to the value associated with the combined parameters. I've been trying to use the sumiff function and the results do not replicate. Any help is welcomed

    1. Sorry, it's not quite clear what you are trying to achieve. To ensure clear understanding of your task, can you provide an example of the source data and the desired result you are aiming for?

  14. Hi, i'm trying to use the SUMIFS / SUM Array but I can't get it working, hope you can help me.

    I want to have the sum of amount with the following 3 conditions;

    for example: The Sum of GL account: 4000 Salaries ,Cost center: General, Period: 2023-1

    1. Column G/L Account
    2. Cost Center:
    3. Period

    This is the formula that i applied: {=SUM(A2:A12="'4000 - Gross salaries NL")*(C2:C12="'GEN MGT")*(D2:D12="2023-1")*(B2:B12)}

    1. Hi!
      I cannot check your formula as I do not have your data. However, if you have dates written in column D, then use such criteria:

      (YEAR(D2:D12)=2023)*(MONTH(D2:D12)=1)

  15. Hello. Please help as im using below formula to get the sum of 3 certain cell ranges containing "BR" and "Y" but I would also like to get the sum of the same cell ranges but containing "CH" and "N" to be added to the first formula. I've used below but the total doesn't give me the right number I'm looking for.

    =SUMIFS(F25:F51,I25:I51,"BR",K25:K51,"Y")+SUMIFS(F25:F51,I25:I51,"CH",K25:K51,"N")

    1. Hi!
      Unfortunately, I don't have your data and can't check this formula. But it looks like it is correct.

  16. How would I write a modified formula for the example using this base formula.

    =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))

    What if I wanted to another set of criteria for "products" to include "cherries" and "apples".

    Essentially, I want the sum of the cross section of 2 sets of criteria in 2 separate criteria ranges. Ideally, I would like to do cell references for the criteria against its range and not hard code the criteria.

      1. Here is the brake down: want a SUMIFS formula that considers the following. I want the sum total of all the cross-sections of 3) & 5)

        1) SUM RANGE: ABC'!CP:CP

        2) Criteria_range1: ABC'!$C:$C

        3) criterion1 (range): A7:A8

        4) Criteria_range2: ABC'!$F:$F

        I hope that makes sense.

        5) criterion1 (range): XYZ'!$C:1$C3

        1. Hi!
          If you use your data in the instructions, which you could read yourself in the article above, we will get the formula

          =SUMIFS(ABC'!CP:CP,C:C,A7,ABC'!C:C,A8,ABC'!F:F,XYZ'!C1,F:F,XYZ'!C2,F:F,XYZ'!C3)

          1. Thanks for the reply!

            However, when I input that formula (which I have tried before) the result returned is "0".

            Maybe it has to do with the fact that I am using Gsheets vs. Excel?

  17. How to use SUMIF formula to collect value for same criterion from multiple sheets in same workbook.

    E.g., Criterion is "TDS 22-23" in column B, Range is column A and value is in column C.
    Value to be summed in from sheet 1 to sheet 4 of workbook in sheet 5.

    Kindly help. Thanks

  18. Hey! I have two columns that I am working with: GL Account | Amount

    The first part of the formula is fine which is =sum(sumif(B:B,{"GL Account","GL Account","GLAccount"},D:D))

    The problem I am running into is that for one of the accounts I only want the number if it is equal to or > than 0 so to the above formula i tried +sumif(B:B,"GL Account",D:D,">=0") but had no luck.

    Please help!

    1. Hi!
      If you have many criteria, use the SUMIFS function:

      =SUMIFS(D:D,B:B,"GL Account",D:D,">=0")

      For more information and examples, see article above.

  19. I need to count minutes with 3 criteria:
    I need to Sum the number of minutes (D cell) if A cell includes text with the required hotel name
    and the time between 08:00 - 19:59 (and 20:00 - 07:59)

    My columns:

    A(Hotel name) B(Start Time) C(End Time) D(Duration)
    AHotel Blabla 17:03:43 17:04:43 1
    BHotel Blabla 17:02:43 17:04:43 2
    AHotel Blabla 08:03:43 08:04:43 1
    CHotel Blabla 08:02:43 08:04:43 2

    I am trying:

    =SUMIFS(D:D,A:A,"*AHotel*",B:B,">=08:00:00",B:B,"<=19:59:00")

    It doesn't work, please help me...

  20. Hello,

    I am trying to total 2 sets of values, but only if their checkboxes are checked. I have a set of checkboxes in cells B9, B11, B13 & B15 and values for these in column E, and another set of checkboxes in cells B26, B28 & B30 with their values also in column E.

    For the first set I have written the formula =SUMIFS(E8:E15, B8:B15, TRUE) in cell F16, which works. However, I only want this to calculate if none of the checkboxes in cells B26, B28 & B30 are checked. What formula should i use instead?

    Also, the values for the second set only need to be included in the total too, if all B9, B11, B13 & B15 checkboxes in the first set are checked, so for this I have written the formula =IF(AND(B9=TRUE,B11=TRUE,B13=TRUE,B15=TRUE),SUM(E8:E15),"") in cell F31, which also works.

    However I want to also include the values of the second set in cell F31 too, but only if their checkboxes (B26, B28 & B30) are also checked. What formula should I use for this?

      1. Hi Alexander,

        Brilliant as ever, that works great, thanks!

  21. Scenario:
    Given data are availment dates, transactions and amounts per transaction

    I wanted to summarize the total for each dates and I have managed to use the =SUMIFS formula.
    However, I wanted to drag down the formula for the rest of the transaction dates and failed have the correct total.

    What should I do with my formula?

    TIA

  22. How can I make this formula below work without having to type/copy/Paste "180/470/560100704-Grants from State Agen" in the formula. I tried to do it with a reference to the cell Directly to the Left "B4" with the exact text of "180/470/560100704-Grants from State Agen" but the formula didn't work. I built this formula but i am sort of a novice at this. There are hundreds of lines that have different codes and text so it is better if i can reference the cell "B4" to make the formula work. A different formula is OK but this is how i figured out how to do this.

    Essentially i have 3,000 lines of a general ledger that i want to summarize into there perspective monthly entries.

    L2 = 2022
    L3 = October

    =SUMIFS(DATA!$G$3:$G$6000,DATA!$C$3:$C$6000,"180/470/560100704-Grants from State Agen",DATA!$E$3:$E$6000,SUMMARY!L3,DATA!$A$3:$A$6000,SUMMARY!L2)

      1. Let me elaborate more. The Formula is Looking at Tab "DATA" at Column C ("C3:C6000") for the Info on B4 (B4 is matching C3:C6000) and Returning info form Column G. It is also checking for a match in L2 and L3 for info in the Data TAB for Column E and A.

        =SUMIFS(DATA!$G$3:$G$6000,
        DATA!$C$3:$C$6000,
        "180/470/560100704-Grants from State Agen",
        DATA!$E$3:$E$6000,
        L3,
        DATA!$A$3:$A$6000,
        L2)

        I want to Replace "180/470/560100704-Grants from State Agen" with a Reference to B4 which you showed me ">"B4. But when i do this instead of returning a summary of $2,734 it is giving me 73,456. This i assume is a summary of the entire column of G Rather than just column G in October of 2022.

        =SUMIFS(DATA!$G$3:$G$6000,
        DATA!$C$3:$C$6000,
        ">"$B4,
        DATA!$E$3:$E$6000,
        L3,
        DATA!$A$3:$A$6000,
        L2)

      2. Thank you that detail (">"B4,)worked. But. It is Suming the entire column it is referencing without considering the other "Ifs" Aka Year(L2) and Month(L3).

        1. To sum not the whole column, add conditions to the SUMIFS formula. You can find all the information you need in the article above. To compare not the whole date, but only the month or year, use the MONTH or YEAR functions.

  23. Hello.

    What I am looking for is means to SUM values based on their concomitant criteria not being part of values already summed up elsewhere...

    e.g.

    [A] [B]
    [1] Bob 100
    [2] Bob 200
    [3] Sue 350
    [4] Tom 220
    [5] Bill -130
    [6] Jane -240
    [7] Jane -87

    COUNTA for COL[A] is going to be 7
    SUM for COL[B] is going to 413

    The grand total needs to be calculated, but values under 0 need to be excluded elsewhere for writing out concatenated text values.

    It took forever to do this using SUM, COUNTA, COUNTIF functions and a separate equation as a name range. But that was so clunky... 😒😯

    My main problem is dealing with SUM for multiple COUNTIF statements.

    Thank you.

  24. Thank you for detailed blog. I am looking for a formula to calculate total qty product wise in column E and F. for example.

    Apples 410
    Bananans 600
    Cherries 530
    Oranges 400

    Can you please help?

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail.

  25. What if I need a 2nd column data pulled? I currently have this formula but need to add an additional data info from a range of columns (from Sanchez!B8:B7)
    =SUMIFS(Sanchez!C8:C67,Sanchez!A8:A67,"<="&Summary!M7)

    1. Hi!
      If I understand your task correctly, try the following formula:

      =SUMIFS(Sanchez!C8:C67,Sanchez!A8:A67,"<="&Summary!M7)+SUMIFS(Sanchez!B8:B67,Sanchez!A8:A67,"<="&Summary!M7)

      Please read the above article carefully.

  26. In the Example Example 3. SUMPRODUCT & SUMIFS with Mike, John and Pete in column H,
    how can you use a single sumproduct function or other function in cell I1 to get the total broken down by Mike, John and Pete?
    SUMIFS can do this, but SUMIFS does not allow other functions within the ranges or criteria.
    It would look like this, the cell with 120 has the single formula that spills down based upon the number of items in column H.
    H I
    Mike 120
    John 250
    Pete 290

  27. i need formula to sum a column in a table based on which are details in two coloumns were nill and i need to make sure there is another column must include a specific text.please help

  28. Hi,

    If there's any text in QTY cell, how i will SUMIF by criteria? i mean i need to ignore text's in QTY column and sum the values in QTY column with a criteria i have in another sheet.

    Thanks

  29. Hi, is it somehow possible to use different size range lengths in SUMIFS

    for example, i have values in the range a1:a10 and i want to sum these values based on two different criteria.
    criteria 1 range=b1
    criteria 1 = "city"
    criteria 2 range=c1
    criteria 2 = "yes"

    =sumifs(a1:a10,b1,"city",c1,"YES")
    this is only a dummy example, i have multiple sheets in which i have to use this kind of values.
    this can be done using the helper column(in every sheet i have to create a helper column), but i want it without a helper column. (maybe possible...)

    thanks

      1. thanks for replay
        any workaround for this problem,,, maybe any other formula for this??

  30. How do I use TRIM formula within SUMIFS formula to clear invisible spaces??

  31. Great place to learn excel

    I have a query

    let's say I have a list of 1000 ordered however, my previous inventory is divided into sets of 100 and each set of 100 units has a unique landing cost now I want to know how I can enter these costs in a formula

    example

    Column A is the list of 1000 order and each cell in the column is having either 1 or 2 written
    Column B needs to add landing cost which is unique for each set of 100 units so It means I have 10 different landing cost

    Please share a formula that can automate the process

  32. sum should equal 20. You should use the + operator

  33. Hello Sir,
    Is there any shortcut for this formula
    SUMIFS(Net_Delivery21,CODE21,A6)+SUMIFS(Net_Delivery21,CODE21,B6)+SUMIFS(Net_Delivery21,CODE21,C6)+SUMIFS(Net_Delivery21,CODE21,D6)+SUMIFS(Net_Delivery21,CODE21,E6)

    Thanks

    2. sorry I mean this one Sir,

      SUMIFS(Z2:Z500,A2:A500,A6)+SUMIFS(Z2:Z500,A2:A500,B6)+SUMIFS(Z2:Z500,A2:A500,C6)+SUMIFS(Z2:Z500,A2:A500,D6)+SUMIFS(Z2:Z500,A2:A500,E6)

      Thanks

  34. Hi!
    As your instruction, we have

    1. =SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
    2. =SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))

    I try to find how I can replace "Pete" in formula 1 into G4 (as the value of G4 is Pete) , the formula would become

    =SUMPRODUCT(SUMIF(C2:C9, {"Mike","John",G4}, D2:D9))

    but it doesn't work, pls help me how fix it!! B/c I want to create an array from specific text and from value of cell

    Tks!!!

  35. How to SUM the Qty automatically in MS Office 2007 Excel if OD, THK & SPECN changed.

    DESCN OD THK SPECN Qty
    Pipe 60.30 5.54 SA 106 GR B 28.26
    Pipe 60.30 5.54 SA 106 GR B 24.6
    Pipe 88.90 5.49 SA 106 GR B 3
    Pipe 88.90 5.49 SA 106 GR B 46.632
    Pipe 88.90 7.62 SA 106 GR B 11.52
    Pipe 88.90 7.62 SA 106 GR B 2
    Pipe 88.90 7.62 SA 106 GR B 3
    Pipe 88.90 7.62 SA 106 GR B 3
    Pipe 88.90 7.62 SA 106 GR B 46.048
    Pipe 88.90 7.62 SA 106 GR B 38.6
    Pipe 88.90 11.13 SA 106 GR B 6.2
    Pipe 114.30 6.02 SA 106 GR B 1
    Pipe 114.30 8.56 SA 106 GR B 7.26
    Pipe 114.30 8.56 SA 106 GR B 3
    Pipe 114.30 8.56 SA 106 GR B 3
    Pipe 114.30 8.56 SA 106 GR B 182.848
    Pipe 114.30 8.56 SA 106 GR B 0.7
    Pipe 114.30 8.56 SA 106 GR B 0.7

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  36. Great work, explain everything very clear with simple example. Thanks alot.

  37. Thanks very much for this great tutorial! I'm trying to create a formula that is a bit beyond my basic skill set and hoping you could provide guidance. I'm trying to pull data from one sheet into another, with multiple parameters as follows:

    One one sheet ("Data 2") I have "$ revenue" in column O and "Campaign Name" in column P " -
    So it looks a bit like this:
    Column O. Column P
    $100. Campaign A
    $50. Campaign A
    $75. Campaign A
    $250. Campaign B
    etc.

    I'm trying to put that into a summary sheet, to sum up revenue by campaign - and match by Campaign Name on that summary sheet (column B).
    So it looks like this
    Col B: Campaign Name Revenue
    Campaign A. (formula here should = $225)
    Campaign B. etc.

    I came up with this: =SUMIF('Data 2'!O:O,B:B,'Data 2'!P:P)

    2. (hit send too soon!) - but that formula is giving me a $0 result for some reason.

      Also, to make things more complex, I'd like to also suppress certain sales stages (sheet Data 2, column Q) from being included. I've tried this, but also is not working (error message, so definitely wrong..):
      =SUMIF('Data 2'!O:O,B:B,'Data 2'!P:P,'Data 2'!Q:Q"Closed Nurture","Closed Won","Closed Lost")

      1. Hi!
        Please re-check the article above since it covers your task.
        If I understand correctly, the formula might look like this:

        =SUMIFS(‘Data '2′'!O:O,’Data '2′'!P:P,B1,’Data '2’'!Q:Q,”<>Closed Nurture”,’Data '2’'!Q:Q,”<>Closed Won”,’Data '2’'!Q:Q,”<>Closed Lost”)

        1. Thank you! That looks exactly right - but I'm still getting a "formula parse error" (?)

          1. Hi!
            I can't check the formula that contains unique references to your workbook worksheets. I have shown you how to modify your formula. Use SUMIFS function.

  38. HI I HAVE FORMULA AS BELOW,

    HOW TO sum up column I only when the value of Column I is less than zero?

    =SUMIFS('DD Policy Due'!I:I,'DD Policy Due'!H:H,Consolidated!D3) * IF(G3="D",1,-1)

      1. Thank you so much but how could I incorporate with my Formula?
        =SUMIFS('DD Policy Due'!I:I,'DD Policy Due'!H:H,Consolidated!D3) * IF(G3="D",1,-1)

        When I am typing as below error is coming
        =SUMIFS('DD Policy Due'!I:I,"<0",'DD Policy Due'!I:I,'DD Policy Due'!H:H,Consolidated!D3) * IF(G3="D",1,-1)

        1. Hi!
          I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

          =SUMIFS(I:I,I:I," < 0",H:H,D3) * IF(G3="D",1,-1)

  39. Thank you very much for this very useful post on Excel formulas for multiple criteria requirement.

  40. Please give me some solution for below requested formula if it is right
    =SUMIFS(A2:E2,">="120000-10%)

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

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

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

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

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

  46. 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*")

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

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

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

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

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