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 365, 2021, 2019, 2016, 2013, 2010, and lower.

As everyone knows, Microsoft Excel provides an array of functions to perform various calculations with data. A few articles ago, we explored COUNTIF and COUNTIFS, which are designed for counting cells based on a single condition and several conditions, respectively. Last week we covered Excel SUMIF that adds values meeting the specified criteria. Now it's time to go over the plural version of SUMIF - Excel SUMIFS that allows summing values by multiple criteria.

Those who are familiar with the SUMIF function might think that converting it to SUMIFS takes just an extra "S" and a few additional criteria. This would seem quite logical… but "logical" it's not always the case when dealing with Microsoft : )

Excel SUMIF function - syntax & usage

The SUMIF function is used to conditionally sum values based on a single criteria. We discussed the SUMIF syntax in detail in the previous article, so let me give you just a quick summary now.

SUMIF(range, criteria, [sum_range])
  • range - the range of cells to be evaluated by your criteria, required.
  • criteria - the condition that must be met, required.
  • sum_range - the cells to sum if the condition is met, optional.

As you see, the syntax of the Excel SUMIF function allows for one condition only. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. How can that be? By adding the results of several SUMIF functions and by using SUMIF formulas with array criteria, as demonstrated in the examples that follow.

Excel SUMIFS function - syntax & usage

You use SUMIFS in Excel to find a conditional sum of values based on multiple criteria. The SUMIFS function was introduced in Excel 2007 and is available in all subsequent versions of Excel 2010, 2013, 2016, 2019, 2021, and Excel 365.

Compared to SUMIF, the SUMIFS syntax is a little bit more complex:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The first 3 arguments are mandatory, additional ranges and their associated criteria are optional.

  • sum_range - one or more cells to sum, required. This can be a single cell, a range of cells or a named range. Only cells with numbers are summed; blank and text values are ignored.
  • criteria_range1 - the first range to be evaluated by the associated criteria, required.
  • criteria1 - the first condition that must be met, required. You can supply the criteria in the form of a number, logical expression, cell reference, text or another Excel function. For example you can use criteria such as 10, ">=10", A1, "cherries" or TODAY().
  • criteria_range2, criteria2, … - these are additional ranges and criteria associated with them, optional. You can use up to 127 range/criteria pairs in SUMIFS formulas.

  • For a SUMIFS formula to work correctly, all the criteria_range arguments must have the same dimension as sum_range, i.e. the same number of rows and columns.
  • The SUMIFS function works with AND logic, meaning that a cell in the sum range is summed only if it meets all of the specified criteria, i.e. all the criteria are true for that cell.

Basic SUMIFS formula

And now, let's have a look at the Excel SUMIFS formula with two conditions. Suppose, you have a table listing the consignments of fruit from different suppliers. You have the fruit names in column A, suppliers' names in column B, and quantity in column C. What you want is to find out a sum of amounts relating to a given fruit and supplier, e.g. all apples supplied by Pete.
Data to be summed with two conditions

When you're learning something new, it's always a good idea to start with simple things. So, to begin with, let's define all the arguments for our SUMIFS formula:

  • sum_range - C2:C9
  • criteria_range1 - A2:A9
  • criteria1 - "apples"
  • criteria_range2 - B2:B9
  • criteria2 - "Pete"

Now assemble the above parameters, and you will get the following SUMIFS formula:

=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
An example of the Excel SUMIFS formula with two conditions

To refine the formula further, you can replace the text criteria "apples" and "Pete" with cell references. In this case, you won't have to change the formula to calculate the quantity of other fruit from a different supplier:

=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)

SUMIF vs. SUMIFS in Excel

Since the aim of this tutorial is to cover all possible ways to sum values by several conditions, we will discuss formula examples with both functions - Excel SUMIFS and SUMIF with multiple criteria. To use them correctly, you need to clearly understand what these two functions have in common and in what way they are different.

While the common part is clear (similar purpose and parameters), the differences are not so obvious, though very essential.

There are 4 major differences between SUMIF and SUMIFS:

  1. Number of conditions. SUMIF can evaluate just one condition at a time while SUMIFS can check for multiple criteria.
  2. Syntax. With SUMIF, the sum_range is the last and optional argument - if not defined, the values in the range argument are summed. With SUMIFS, sum_range is the first and required argument.
  3. Size of ranges. In SUMIF formulas, sum_range does not necessarily have to be of the same size and shape as range, as long as you have the top left cell right. In Excel SUMIFS, each criteria_range must contain the same number of rows and columns as the sum_range argument.

    For example, SUMIF(A2:A9,F1,C2:C18) will return the correct result because the leftmost cell in the sum_range argument (C2) is right. So, Excel will make the correction automatically and include as many columns and rows in sum_range as there are in range.

    A SUMIFS formula with unequally sized ranges will return a #VALUE! error.

  4. Availability. SUMIF is available in all Excel versions, from 365 through 2000. SUMIFS is available in Excel 2007 and higher.

Alright, enough strategy (i.e. theory), let's get into the tactics (i.e. formula examples : )

How to use SUMIFS in Excel - formula examples

A moment ago, we discussed a simple SUMIFS formula with two text criteria. In the same manner, you can use Excel SUMIFS with multiple criteria expressed by numbers, dates, logical expressions, and other Excel functions.

Example 1. Excel SUMIFS with comparison operators

In our fruit suppliers table, suppose, you want to sum all deliveries by Mike with Qty. 200 or more. To do this, you use the comparison operator "greater than or equal to" (>=) in criteria2 and get the following SUMIFS formula:

Excel SUMIFS formula with comparison operators

Note. Please pay attention that in Excel SUMIFS formulas, logical expressions with comparison operators should always be enclosed in double quotes ("").

We covered all possible comparison operators in detail when discussing Excel SUMIF function, the same operators can be used in SUMIFS criteria. For example, the following formula with return the sum of all values in cells C2:C9 that are greater than or equal to 200 and less than or equal to 300.

=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")

Example 2. Using Excel SUMIFS with dates

In case you want to sum values with multiple criteria based on the current date, use the TODAY() function in your SUMIFS criteria, as demonstrated below. The following formula sums values in column D if a corresponding date in column C falls within the last 7 days, including today:

=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
Excel SUMIFS formula for dates

Note. When you use another Excel function together with a logical operator in the criteria, you have to use the ampersand (&) to concatenate a string, for example "<="&TODAY().

In a similar fashion, you can use the Excel SUMIF function to sum values in a given date range. For example, the following SUMIFS formula adds the values in cells C2:C9 if a date in column B falls between 1-Oct-2014 and 31-Oct-2014, inclusive.

=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")

The same result can be achieved by calculating the difference of two SUMIF functions, as demonstrated in this example - How to use SUMIF to sum values in a given date range. However, Excel SUMIFS is much easier and more understandable, isn't it?

Example 3. Excel SUMIFS with blank and non-blank cells

When analyzing reports and other data, you may often need to sum values corresponding either to empty or non-empty cells.

Criteria Description Formula Example
Blank cells "=" Sum values corresponding to blank cells that contain absolutely nothing - no formula, no zero length string. =SUMIFS(C2:C10, A2:A10, "=", B2:B10, "=")

Sum values in cells C2:C10 if the corresponding cells in columns A and B are absolutely empty.

"" Sum values corresponding to "visually" blank cells including those that contain empty strings returned by some other Excel function (for example, cells with a formula like =""). =SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Sum values in cells C2:C10 with the same conditions as the above formula, but includes empty strings.

Non-blank cells "<>" Sum values corresponding to non-empty cells, including zero length strings. =SUMIFS(C2:C10, A2:A10, "<>", B2:B10, "<>")

Sum values in cells C2:C10 if the corresponding cells in columns A and B are not empty, including cells with empty strings.

Sum values corresponding to non-empty cells, not including zero length strings. =SUM(C2:C10) - SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

=SUM((C2:C10) * (LEN(A2:A10)>0)*(LEN(B2:B10)>0))

Sum values in cells C2:C10 if the corresponding cells in columns A and B are not empty, cells with zero length strings are not included.

And now, let's see how you can use a SUMIFS formula with "blank" and "non-blank" criteria on real data.

Suppose, you have an order date in column B, delivery date in column C and Qty. in column D. How do you find the total of products that have not been delivered yet? That is, you want to know the sum of values corresponding to non-empty cells in column B and empty cells in column C.

The solution is to use the SUMIFS formula with 2 criteria:

=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Excel SUMIFS formula for blank and non-blank cells

Using Excel SUMIF with multiple OR criteria

As noted in the beginning of this tutorial, the SUMIFS function is designed with AND logic. But what if you need to sum values with multiple OR criteria, i.e. when at least one of the conditions is met?

Example 1. SUMIF + SUMIF

The simplest solution is to sum the results returned by several SUMIF functions. For example, the following formula demonstrates how to find the total of products delivered by Mike and John:

=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Excel SUMIF formula with multiple OR criteria

As you see, the first SUMIF function adds the quantities corresponding to "Mike", the other SUMIF function returns the amounts relating to "John" and then you add these 2 numbers.

Example 2. SUM & SUMIF with an array argument

The above solution is very simple and may get the job done quickly when there are only a couple of criteria. But a SUMIF + SUMIF formula may grow up enormously if you want to sum values with multiple OR conditions. In this case, a better approach is using an array criteria argument in the SUMIF function. Let's examine this approach now.

You can start by listing all of your conditions separated by commas and then enclose the resulting comma-separated list in {curly brackets}, which is technically called an array.

In the previous example, if you want to sum the products delivered by John, Mike and Pete, your array criteria will look like {"John","Mike","Pete"}. And the complete SUMIF function is SUMIF(C2:C9, {"John","Mike","Pete"} ,D2:D9).

The array argument consisting of 3 values forces your SUMIF formula to return three separate results, but since we write the formula in a single cell, it would return the first result only - i.e. the total of products delivered by John. To get this array-criteria approach to work, you have to use one more little trick - enclose your SUMIF formula in a SUM function, like this:

=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Using SUM & SUMIF with an array argument to sum values with multiple OR criteria

As you see, an array criteria makes the formula much more compact compared to SUMIF + SUMIF, and lets you add as many values as you like in the array.

This approach works with numbers as well as with text values. For instance, if instead of the suppliers' names in column C, you had supplier IDs like 1, 2, 3 etc., then your SUMIF formula would look similar to this:

=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))

Unlike text values, numbers needn't be enclosed in double quotes in array arguments.


In case, your preferred way is to list the criteria in some cells rather that specify them directly in the formula, you can use SUMIF in conjunction with the SUMPRODUCT function that multiplies components in the given arrays, and returns the sum of those products.


Where G2:G4 are the cells containing your criteria, the suppliers' names in our case, as illustrated in the screenshot below.

But of course, nothing prevents you from listing the values in an array criteria of your SUMIF function if you want to:

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

The result returned by both formulas will be identical to what you see in the screenshot:
Sum with multiple criteria using the SUMPRODUCT / SUMIF formula

Excel SUMIFS with multiple OR criteria

If you want to conditionally sum values in Excel not simply with multiple OR conditions, but with several sets of conditions, you will have to use SUMIFS instead of SUMIF. The formulas will be very similar to what we've just discussed.

As usual, an example might help to illustrate the point better. In our table of fruit suppliers, let's add the Delivery Date (column E) and find the total quantity delivered by Mike, John and Pete in October.

Example 1. SUMIFS + SUMIFS

The formula produced by this approach includes a lot of repetition and looks cumbersome, but it is easy to understand and, most importantly, it works : )

=SUMIFS(D2:D9,C2:C9, "Mike", E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014") +
SUMIFS(D2:D9, C2:C9, "John", E2:E9, ">=10/1/2014", E2:E9, "<=10/31/2014") +
SUMIFS(D2:D9, C2:C9, "Pete", E2:E9, ">=10/1/2014" ,E2:E9, "<=10/31/2014")

As you see, you write a separate SUMIFS function for each of the suppliers and include two conditions - equal to or greater than Oct-1 (">=10/1/2014",) and less than or equal to Oct 31 ("<=10/31/2014"), and then you sum the results.
Add several SUMIFS functions to sum values with multiple OR conditions

Example 2. SUM & SUMIFS with an array argument

I've tried to explain the essence of this approach in the SUMIF example, so now we can simply copy that formula, change the order of arguments (as you remember it is different in SUMIF and SUMIFS) and add additional criteria. The resulting formula is more compact than SUMIFS + SUMIFS:

=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))

The result returned by this formula is exactly the same as you see in the screenshot above.


As you remember, the SUMPRODUCT approach differs from the previous two in the way that you enter each of your criteria in a separate cell rather that specify them directly in the formula. In case of several criteria sets, the SUMPRODUCT function won't suffice and you will have to employ ISNUMBER and MATCH as well.

So, assuming that the Supplies Names are in cells H1:H3, Start Date is in cell H4 and End Date in cell H5, our SUMPRODUCT formula takes the following shape:

=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
The SUMPRODUCT formula to sum values with multiple OR conditions

Many people wonder why use double dash (--) in SUMPRODUCT formulas. The point is that Excel SUMPRODUCT ignores all but numeric values, while the comparison operators in our formula return Boolean values (TRUE / FALSE), which are non-numeric. To convert these Boolean values to 1's and 0's, you use the double minus sign, which is technically called the double unary operator. The first unary coerces TRUE/FALSE to -1/0, respectively. The second unary negates the values, i.e. reverses the sign, turning them into +1 and 0, which the SUMPRODUCT function can understand.

I hope the above explanation makes sense. And even if it doesn't, just remember this rule of thumb - use the double unary operator (--) when you are using comparison operators in your SUMPRODUCT formulas.

Using Excel SUM in array formulas

As you remember, Microsoft implemented the SUMIFS function in Excel 2007. If someone still uses Excel 2003, 2000 or earlier, you will have to use a SUM array formula to add values with multiple AND criteria. Naturally, this approach works in modern versions of Excel 2013 - 2007 too, and can be deemed an old-fashioned counterpart of the SUMIFS function.

In the SUMIF formulas discussed above, you have already used array arguments, but an array formula is something different.

Example 1. Sum with multiple AND criteria in Excel 2003 and earlier

Let's get back to the very first example where we found out a sum of amounts relating to a given fruit and supplier:
Source table to sum values by several conditions

As you already know, this task is easily accomplished using an ordinary SUMIFS formula:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")

And now, let's see how the same task can be fulfilled in early "SUMIFS-free" versions of Excel. First off, you write down all the conditions that should be met in the form of range="condition". In this example, we have two range/condition pairs:

Condition 1: A2:A9="apples"

Condition 2: B2:B9="Pete"

Then, you write a SUM formulas that "multiplies" all of your range/condition pairs, each enclosed in brackets. The last multiplier is the sum range, C2:C9 in our case:

=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))

As illustrated in the screenshot below, the formula perfectly works in the latest Excel 2013 version.
An example of using Excel SUM in an array formula

Note. When entering any array formula, you must press Ctrl + Shift + Enter. Once you do this, your formula gets enclosed in {curly braces}, which is a visual indication that an array formula is entered correctly. If you try typing the braces manually, your formula will be converted to a text string, and it won't work.

Example 2. SUM array formulas in modern Excel versions

Even in modern versions of Excel, the power of the SUM function should not be underestimated. The SUM array formula is not simply gymnastics of the mind, but has a practical value, as demonstrated in the following example.

Suppose, you have two columns, B and C, and you need to count how many times column C is greater than column B, when a value in column C is greater or equal to 10. An immediate solution that comes to mind is using the SUM array formula:

=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Using SUM array formulas in modern Excel versions

Don't see any practical application to the above formula? Think about it in another way : )

Suppose, you have the orders list like shown in the screenshot below and you want to know how many products have not been delivered in full by a given date. Translated into Excel's language, we have the following conditions:

Condition 1: A value in column B (Ordered items) is greater than 0

Condition 2: A value in column C (Delivered) in less than in column B

Condition 3: A date in column D (Due date) is less than 11/1/2014.

Putting the three range/condition pairs together, you get the following formula:

An example of SUM array formula in Excel 2013

Well, the formula examples discussed in this tutorial have only scratched the surface of what Excel SUMIFS and SUMIF functions can really do. But hopefully, they have helped pointing you in the right direction and now you can sum values in your Excel workbooks no matter how many intricate conditions you have to consider.

You may also be interested in

521 comments to "How to use Excel SUMIFS and SUMIF with multiple criteria"

  1. Stephen says:

    I am trying to create a formula that does the following and I cant get the formula to work for all. only one or two of them. but not all of them.
    Need a sum for Column J
    if Column E = the words Growth or Acquisition
    if Column K = the month of July, 2019
    So far I have this;

    But its counting the amounts multiple times

    • Stephen says:

      I have an updated formula. It is working and not erring out or counting the amounts multiple times however its not catching all amounts.

  2. Mariann says:

    How could I sum two ranges: C20:I20 and C22:I22 using two Criteria ranges: C19:I19 and C21:I21 ... If if any of the cells in the criteria ranges have the criteria "PTO"?

    I have tried a few these, but have not been successful:
    =SUMIFS(C20:I20, C19:I19,"PTO”) + SUMIFS(C22:I22, C21:I21,”PTO")
    =SUMIFS(C20:I20, C19:I19,"PTO”,[C22:I22, C21:I21])
    Essentially, I have this working formulas but need to add another Sum_range and Criteria_range to the formula.

    Thanks in advance!

  3. Krishnakumar says:

    I want to make the criteria as dynamic by getting the input from user via slicer. So I tried using sum before sumifs and if I hard code the multiple criteria on the same column it works =sum(sumifs(sum_range, criteria range, {"a","b"}) but if I want to get the input from user via slicer which reference to a cell it doesn't work. =Sum(sumifs(sum_range, criteria range, name_manager).
    Name manager to a input which I receive it from user via slicer.

  4. Nenad says:

    can it be used for the Criteria Range the full column for example as I cant get the formula with it. I cant have the exact range as the range is changing from day to day and there is 29000 lines.

  5. Asela says:

    No. of months Amount
    12 14
    20 14
    12 9
    15 18
    25 14
    68 18
    70 9

    Hi Svetlana,
    Could you please help me on following,
    What is the formula for If i want to calculate total amount in(Column 2) for 12 to 25 months (Column 1) ?
    I am greatly appreciate if you could help me on this. Thank you

  6. Nick Panagiotopoulos says:

    My congratulations for this excellent article. I already use some criteria for my company.
    What I haven't understood is the following: In the formula
    =SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
    the MATCH syntax should say 'Take the value H1:H3, put it to C2:C9, with exact match, eg
    --(ISNUMBER(MATCH(H1:H3, C2:C9,0))), D2:D9)
    But this gives an error. Why?
    Again congratulations for your really excellent article!

  7. Chris says:

    For the second example, if i wanted to return multiple Criteria 1 for example Apples and Oranges Pete has supplied how would I do that?

    • Hi Chris,
      You can do that with one of the below formulas:
      =SUM(SUMIFS(C2:C9, A2:A9, {"apples","oranges"}, B2:B9,"pete"))

      =SUMIFS(C2:C9, A2:A9,"apples", B2:B9, "pete") + SUMIFS(C2:C9, A2:A9,"oranges", B2:B9, "pete")

      • Chris says:

        Thanks for that it was perfectly. although in some instances i have over 20 criteria and calculating is taking quite a long time. is there a faster way to calculate?

  8. Sunil Poojari says:

    This log has really helped me finding something valuable & usefull, which I may not aware about.


  9. Milo says:

    I'm trying to figure out a way to make something like this work but I am unable to do so. Please, will someone help me?
    A B C
    1 Amounts Condition Result
    2 amount 1 if C1=text and B2=empty cell, enter A2; if C1=text and B2=not empty cell, enter A2; if C1=numbers and B2=empty cell enter C1+A2; if C1=numbers and B2=not empty cell enter C1-A2;
    3 amount 2 if C2=text and B3=empty cell, enter A3; if C2=text and B3=not empty cell, enter A3; if C2=numbers and B3=empty cell enter C2+A3; if C2=numbers and B3=not empty cell enter C2-A3;
    4 amount 2 * if C3=text and B4=empty cell, enter A4; if C3=text and B4=not empty cell, enter A4; if C3=numbers and B4=empty cell enter C3+A4; if C3=numbers and B4=not empty cell enter C3-A4;
    5 amount 3 h if C4=text and B5=empty cell, enter A5; if C4=text and B5=not empty cell, enter A5; if C4=numbers and B5=empty cell enter C4+A5; if C4=numbers and B5=not empty cell enter C4-A5;
    6 amount 4 if C5=text and B6=empty cell, enter A6; if C5=text and B6=not empty cell, enter A6; if C5=numbers and B6=empty cell enter C5+A6; if C5=numbers and B6=not empty cell enter C5-A6;
    7 amount 5 6 if C6=text and B7=empty cell, enter A7; if C6=text and B7=not empty cell, enter A7; if C6=numbers and B7=empty cell enter C6+A7; if C6=numbers and B7=not empty cell enter C6-A7;

    So the end result would look and work like this:
    A B C
    1 Amounts Condition Result
    2 10 10
    3 15 25
    4 20 * 25
    5 25 yes 25
    6 30 55
    7 35 6 55

  10. Prakash says:

    MR Severity Category Ageing in Days
    Bhavnagar NSA Logical Alarm-OMC >3 Days Feedback
    Surat SA Physical Alarm - BSS >3 Days OMC Logical
    Surat SA Physical Alarm - BSS >3 Days OMC Logical
    Ahmedabad SA Physical Alarm - BSS >3 Days RING SPLITTING
    Ahmedabad SA Physical Alarm - BSS >3 Days RING SPLITTING
    Rajkot NSA Logical Alarm-OMC >3 Days OMC Logical
    Ahmedabad SA Physical Alarm - BSS >3 Days TXPL
    Ahmedabad SA Physical Alarm - BSS >3 Days TXPL
    Baroda SA FAN-Alarm >3 Days FAN FAILURE
    Rajkot NSA Logical Alarm-OMC >3 Days wip
    Rajkot SA Physical Alarm - BSS >3 Days wip
    Ahmedabad NSA Logical Alarm-OMC >3 Days TCTS
    Ahmedabad NSA Logical Alarm-OMC >3 Days TCTS
    Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
    Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
    Baroda NSA Logical Alarm-OMC >3 Days VBS_LL services
    Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
    I am using Sumifs formulea for this attached ment in 2nd Sheet and getting corrected summation pls help to resolved this for 2nd sheet

  11. akila says:

    i have a list of value is about to 100. i want to know the 6 values which will give a sum of a particular value,
    i want to know 6 values in a above values which give total of 165289.
    pls give a solution,

  12. Roz says:


    Can you please help, trying to sum if using multiple criteria including a date range. However it is returning 0.00 value. The date range seems to be the issue as when I remove this the other criteria works. Going around in circles and not sure what I am doing wring.

    =SUMIFS('Detail Transaction Listing'!$J:$J,'Detail Transaction Listing'!$D:$D,$B$2,'Detail Transaction Listing'!$A:$A,B15,'Detail Transaction Listing'!$H:$H,">="&Sheet1!$B$5,'Detail Transaction Listing'!$H:$H,"
    Sheet 1 B5 & C5 is the criteria field.

    Really appreciate your help/

  13. Gijs says:

    I hoped to have finally found the solution, but it regrettably failed.

    In this formula B27, C27 and D27 are the criteria. It works well, ONLY when all criteria cells (B27, C27 and D27) are populated

    I was hoping that if one or two of the criteria cells is empty, it would still add $H$38:$H$275 based on whatever criteria (B27 and/or C27 and/or D27) is in place. Instead, the result I'm getting is 0 if one of the criteria is empty.

    Any ideas how to solve this?

  14. Ateeq ur Rehman says:

    =SUMIFS(H2:H1000, D2:D1000,"vehari", G2:G1000, "short")

    I want to calculate a range...when two conditions are ture..

    But this formula is not working.

    Kindly help.

  15. Matt says:

    How can I get a count of the number of rows returned using the sumifs function?
    My current function is
    =SUMIFS $B$2:$B$105,$C$2:$C$105,O111,$D$2:$D$105,O115,$E$2:$E$105,O113)
    and I would like to know how many rows were returned to get my total that was returned with the sumifs function.

  16. Levi says:

    This tutorial was a massive help!
    Easy to understand and follow, and it works!:)
    Thanks so much for providing this for the public!

  17. shiva says:

    Hi,I need SUMIFS formula with multiple columns reference.column1-type, column2-length adress, column3- lenghcount. now need total count value with each length adress(column2)

  18. Aditya says:

    Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.

    can someone please help

  19. Aditya says:

    Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.

    can some please help

  20. KA says:

    Hey people can you tell us how to use sumif for the following;

    Criteria 1 - Apple
    Criteria 2 -Orange
    Criteria 3 -Lemon
    Criteria 4 -Pineapple

    we want to use sum if to total the sum of "Apples" And all others in s single cell.


    In Cell A1
    =sumif(criteria range,"Apple",sum range)

    In Cell A2
    =sumif(criteria range,"Apple",sum range)

    this above formula is not working??? to be specific it sometimes work

    also tell us can we apply this formula across sheets in the same excel work book?

    awaiting your wise response....

  21. chembe says:

    I am trying to make work easier for my team by building a formula that adds up data based on two criteria: The month (this is okay). Each month has for to five weeks. I want the formula to add data in each months for all weeks in the month but up to the current week. I did the formula below. It however appears that Excel does not recognise the last argument below which uses reference to a cell:

    =SUMIFS('Weekly targets'!$F$14:$BE$14,'Weekly targets'!$F$1:$BE$1,'Summary by Product'!$E$3,'Weekly targets'!$F$3:$BE$3,"<=$C$4")

  22. Dildar says:

    I need help to put this note in this formula.


    Note: { Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-

  23. Robbie says:

    Hello everyone,

    Getting to grips with SUM and SUMIF and have been working on a particular problem using:


    For this means if "PGTA3" is shown in cell K10 then divide P10 by 2 and subtract the result from O10. However, in the cell K10 I would like this to not be limited to "PGTA3" but anything that includes "PGTA" or "GRP" within the cell.

    Essentially O10 is a room size, P10 is a number of students but if ""PGTA or "GRP" is in cell K10 that means there is a second group in another row and the number of students present in the room would be divided in half. Cell K10 could say "PGTA12, "PGTA2", "PGTA3", "GRP1", "GRP2" or a combination of PGTA fields (like "PGTA1,PGTA1" or something like ",PGTA1". The presence of a comma is of no interest to me; if a comma were in a cell alone (it could happen) I want that ignored so I don't think a pure wildcard would work (ie if ANY text in cell K10, divide P10 in half). "PGTA*" and "*" and "*PGTA*" doesn't seem to work anyway and doesn't allow for "GRP". Any help very gratefully received! I aced indexing and array formulas this morning so I'm sure I can ace this too. :)

  24. Manish Gupta says:

    =SUMIFS('Priority & Standard Cost'!$AJ:$AJ,'Priority & Standard Cost'!$B:$B,Sheet3!B3,'Priority & Standard Cost'!$M:$M,">=04-01-2017",'Priority & Standard Cost'!$M:$M,"='Contract Details'!E3",'Priority & Standard Cost'!$M:$M,"<='Contract Details'!F3")
    What is the difference in both formulas.
    I have just changed the date from cell in which date is mentioned in same format....but result is 0....Why?

  25. Neftali Bautista says:

    This is my formula:
    =SUMIF($C$10:$C$30,"*" & $C$7 &"*",D10:D30)

    C7=1 just one number, referring to a shift. So it could be 2 or 3.
    D Column = Are just values

    Any suggestions.

  26. gray says:

    What expression do I need to use, to do the following for +100 different criteria, that counts up all quantities in the different criteria's.

    =SUM(SUMIF(B2:B152, {"Malus"}, D2:D152))
    =SUM(SUMIF(B2:B152, {"Pyrus"}, D2:D152))

    to display on the worksheet
    Malus 25
    Pyrus 36
    etc etc for +100 different criteria

    Its looking more like I am going to have to use VB code

  27. Tracey says:

    one more time:

    =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “= 07/01/2018”, “<=07/31/2018”)

  28. Tracey says:

    I am having problems with my SUMIFS. I need to bring the Invoice Amount from two different worksheets over to my main sheet, but I only want them if they match my GL Code and my Monthly Date.

    =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “=07/01/2018”, “<=07/31/2018”)


    =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date], MonthlyExpensesSummary[July])
    +SUMIFS(Other[Check Amount], Other[G/L Code], MonthlyExpensesSummary[@[G/L Code]], Other[InvoiceDate],[July])

    But, neither one work. The top one I can at least get in without error, but doesn't give me results.

    Can you help??

    Thanks for any assistance,


    • Tracey says:

      That first one has a typo - it is actually like this:

      =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “= 07/01/2018”, “<=07/31/2018”)

  29. Savo says:

    I am doing multiple calculation and cannot make correct combination of formulas for the following example:

    Column1 Column2
    4 x
    4 x
    6 x
    6 x

    I need formula that will conunt criteria from first column without repetition and only for fow that has mark "X" in second column.

    For count of first column I used following formula =SUM(IF(FREQUENCY(...), but I cannot cmbinate it with IF second column has "X".
    Thanks in advance

  30. Praveen Kumar Singh says:

    How to calculate Total sales of Google and Costco customer in North East region???

    Date Region Sales Rep Customer Product COGS Sales
    6/16/2006 NorthEast Jon Google QJV Item $1,835.90 $3,165.34
    6/15/2006 NorthEast Jon ZET Item $6,971.50 $9,295.33
    6/15/2006 SouthEast Chin ZON Item $5,406.27 $9,163.17
    6/15/2006 SouthEast Chin Solar and Wind Inc. JFA Item $736.25 $836.65
    6/14/2006 NorthEast Jeri Costco ZET Item $2,065.11 $4,589.14
    6/14/2006 NorthEast Troung Sherman Williams LZB Item $2,553.28 $3,754.82
    6/14/2006 NorthEast Steven Peet's Coffee EJL Item $2,315.43 $3,405.05

  31. Roche says:

    Good day

    If you could please advise.
    I am trying to do a sumifs using three criteria, but the formula only allows me to add two criteria for evaluation?

    Is there a version of excel that allows this, or is there a combination formula that I could use?

    Thanking you in advance.

  32. Anna says:

    F = Name , A = Value , B = Value
    I use this formula when I want to know the value which is under 20). This is fine. All I wanna know is how can I calculate the value which between 20 to 50. Please help me out.

  33. Salim says:

    Svetlana Cheusheva, you are fantastic. This was quite a piece of valuable information. Thanks a lot, you made my day.

  34. David R says:

    I have a person information spreadsheet with about 2000 rows. I need a formula that will total the 'Sex' and 'Race' columns for the gender and race details. Ex. White Males = ?
    Black Males = ? White Females = ?. The single criteria in the 'Sex' column will be either M or F. The 'Race' criteria will be a single identifier for the race. This seems to be a common problem but I cannot make it work. Thanks for any help.

  35. BHASKAR says:

    Rubber Material Qty1 Cushion Gum Qty2
    LL215-128 11.5 CUS GUM 225 MM-EB 1.3
    LL215-128 11 CUS GUM 200 MM 1.3
    60-62-18-P-Mtk 2.5 CUS GUM 200 MM 1.3
    NH165-103 3/4 6.6 CUS GUM 165 MM 0.6
    FR175-119 6.8 CUS GUM 165 MM 0.7
    NH165-103 3/4 6.6 CUS GUM 165 MM 0.7
    FR175-119 6.8 CUS GUM 165 MM 0.8
    NH165-103 3/4 6.7 CUS GUM 165 MM 0.6
    FR175-119 6.8 CUS GUM 165 MM 0.7
    NH165-103 3/4 6.8 CUS GUM 165 MM 0.6
    NH165-103 3/4 6.7 CUS GUM 165 MM 0.6
    LG-215-128-VT 10.2 CUS GUM 225 MM-EB 1.5
    LG-215-128-VT 10.1 CUS GUM 225 MM-EB 1.4
    LG-215-128-VT 10.1 CUS GUM 225 MM-EB 1.5
    SM 220-128 12.4 CUS GUM 225 MM-EB 1.4
    SM 240-130 12.4 CUS GUM 225 MM-EB 1.5
    SM 230-130 12.7 CUS GUM 225 MM-EB 1.5
    SM 230-130 12.7 CUS GUM 215 MM-EB 1.5
    SM 220-130 12.4 CUS GUM 225 MM-EB 1.4
    SM 230-130 12.8 CUS GUM 215 MM-EB 1.4
    SM 230-130 12.7 CUS GUM 225 MM-EB 1.4
    SM 230-130 12.7 CUS GUM 215 MM-EB 1.5
    Rl 210-127 11.4 CUS GUM 200 MM 1.4
    Rl 210-127 11.4 CUS GUM 200 MM 1.5


    CUS GUM 165 MM
    CUS GUM 200 MM
    CUS GUM 215 MM-EB
    CUS GUM 225 MM-EB
    NH165-103 3/4
    Rl 210-127
    SM 220-128
    SM 220-130
    SM 230-130
    SM 240-130

  36. abdul gafoor says:

    i need sumifs function -- my company loaded flour every day 14 trucks for different companeis . so i need each company howmany ton per day? so please help me

  37. Ruth says:

    I am doing a multiple criteria SUMIFS but with one range I'd like it to be an OR. (The works great for multiple if there are only one or two NOTS, but there are several 'not wanted' and only 2 wanted.)
    I can do a SUMIFS + SUMIFS but the braces option do not seem to work for a cell reference being used for the criteria:
    =SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,"="&DropDowns!$A$28 ) + SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,"="&DropDowns!$A$27 )

    but want to do something like this (since all other criteria is the same)
    ... $H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal"
    =SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,{"="&DropDowns!$A$28,"="&DropDowns!$A$27} )

    is it placement? Does the 'or' criteria need to be first? (the Or function does not work here)

    Is there another sequence for the braces? {"="&DropDowns!$A$28,"="&DropDowns!$A$27}

    The weird "="&cell is the only way my system will allow the criteria in that cell to be used maybe since it is a text.

    Thank you for any insight.

  38. Krystal says:

    I would like to sum the Expected Payment column if the date in the Payment Due Date column is a date in 2018. I have tried both sumif and sumproduct, however, I believe that since the columns I am referencing are formulas which generate a $, date, or blank, it is not working.
    K13:K36=Expected Payment (is a formula that generates a $ or blank) L13:L36=Payment Due Date (is a formula that generates a date of blank)
    If someone has an answer for this, it would be greatly appreciated! :)

  39. Mahaboob says:

    Hi Svetlana Cheusheva,

    Hope you are doing good.....

    I have couple of question on Sum formula.
    Sum it to total
    Sumif is to total with one condition/Criteria
    Sumifs is to total with one or more condition/criteria that to if all condition/criteria satisfy only then only it display the results that is output. my question it should give the output or result even if any one condition/criteria satisfy also.

    Thanks in advance

  40. Andrew says:

    Hello, please can someone help me?
    I have 2 sheets, one with the current sales by location and the other sheet exactly the same but without figures. I basically need to model it for different scenarios.
    This is how it works in my head but not in reality.
    =IF(Scenarios!B1="1",SUMIF('Project Sales month only'!C:C,Scenarios!C3,'Project Sales month only'!I:I*2))
    I have 3 different scenarios so will need to add the other 2 if statements on but please can someone help me?
    Thanks in advance

  41. Guilherme says:

    Hi, I am trying to SUMIF the following:

    Channel, Product and Price

    Under channel column I have different criteria: agent, google, organic, etc.
    Under product: 5 different products/criteria
    Under price: products actual prices that I want to get it summed accordingly.

    i.e.: Channel range. I want to sum all google leads for the various products.

    for Google sales, MBA products only, Google sales, Postgraduate Certificates, etc.

    This is what I am doing but it is not summing correctly.

    =SUMIFS(J2:J40,$B$2:$B$40, "google",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40,"facebook",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40,"organic",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40, "affiliate",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40, "alumni",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40, "referral",$F$2:$F$40,"PGC")

    In the end I want to find out how much sales we got from google for a particular product.

    Can you help, please?



    • Ruth says:

      Guiherme, Would it work if you put the absolute$ reference on the J2:J40 AND put a space before and after each of the + symbols ?

      was >>>SUMIFS(J2:J40,$B$2:$B$40, "google",$F$2:$F$40,"PGC")+SUMIFS(J2:J40...

      try>>> SUMIFS($J$2:$J$40,$B$2:$B$40,"google",$F$2:$F$40,"PGC") + SUMIFS($J$2:$J$40...

      a space before and after the + symbol may help the formula work consistently.

      Hope something works out for you.

  42. Chris says:

    I've found help on with my Excel questions on a number of occasions, and I believe it's important to leave feedback. Thank you for providing clear instructions with images and examples on a variety of specific questions I had. I'm sure I'll be coming back again soon.

  43. Felix Tac says:

    Am glad to find help here.

    I have cells from A-H containing grades. E.g: A=9, B=3, C=2, D=7, E=3, F=5, G=1, H=2.

    How can I use excel to sum only the best six grades out of the eight grades?
    So that total best six grades will be 16.


  44. Ponlork says:

    I have a questions
    I have many categories and want to sum the categories by the months. how can i do to calculate it?

    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  45. mina says:

    From Verious month i need YTD sum upto selected month.

    select Mar MTD YTD
    Jan 200 500 1000
    Feb 300
    Mar 500
    Apr 300
    send me the formula for YTD 1000 get;while MTD is Mar

    • Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help.

  46. Rjahir says:

    how can i sum of 2 cell if above cell has "if" formulas.

    for ex. if c2 sum has if formula
    and c3 has to if formula
    how can i put total of above 2 in c4 cell?

  47. Natalia says:

    Hello, could someone please help me to get a formula when I need to sum up values in one column based on criteria in another column not equal to "John". In other words, I want to sum all the values for Mike, Pete but not John.

    Thank you!

  48. Eddie says:

    Hi Natalia,

    Hope your well.

    I'm trying to sum up Quantities based on date range.

    In my workbook, I have the following worksheets


    Within “Stock” worksheet I have two cells with dates, $B$2 start date, $C$2 End Date, and part number in cell B21 for example

    Within “Customer” worksheet I have named ranges

    This formula, with multiple criteria’s works perfectly

    =IF($B$2=$C$2,SUMIFS(OrderQTY, CustomerPN,B21,DateDue,$B$2),(SUMIFS(OrderQTY, CustomerPN,B21,DateDue,$B$2)+SUMIFS(OrderQTY, CustomerPN,B21,DateDue,$C$2)))

    ie if start date in B2 equals end date in C2, then give result for B2 date only, else give result based on dates between B2 and C2

    Note “B21” is my lookup value

    however I’m guessing it can be written shorter.

    I’ve tried
    =SUMIFS(OrderQTY, CustomerPN,B21,DateDue,”> =”&$B$2, DateDue,”< =”&$C$2) but the result returned is zero

    Can you see what I’m doing wrong, or is the longer version of the formula best?


      • Eddie says:

        Hahaha... it seems both Svetlana and Natalia are responding to various people in this blog, so sorry for my confusion ladies, my respect to both of you, when you get a chance and if either one of you can reply I would appreciate much

  49. Christer says:


    I need help writting a formula that adds the top 4 values out of 5.

    I think it is a sumproduct and a large formula but I can't seem to get it to work.
    =SUMPRODUCT(LARGE(B6:f6,{1,2,3,4})) is not working for me?

    Any ideas?

    Many thanks.


  50. TeresaT says:

    ActiveCell.FormulaR1C1 = "=RC[-1]-SUMIF(R9C14:R90C14, RC[-3], R9C11:R90C11)" THIS WORKS FINE

    But - I need it to look as to columns

    RC[-1] is the total hours a crew is available
    R9C11:R90C11 is a column containing the hours required for each job

    I need the hours from Column 11 to subtract from the total crew hours available if:

    Column 14 OR Column 15 matches the crew name in column rc[-3]

    ActiveCell.FormulaR1C1 = "=RC[-1]-SUMIFS(R9C11:R90C11, R9C14:R90C14, RC[-3], R9C15:R90C15, RC[-3])"

  51. Darsan says:

    if cell A has a value then cell k = CELL D - CELL A
    If cell B has a value then cell k = CELL D + CELL B

    some one please help me with a formula for the above.

    • Hi, Darsan,

      if your value is in A2, and you need to check whether it contains a number, put the following into K cell where you want to see the result:

      in case you need to see if there's anything at all in the cell, use the following:

      Please adjust the formula for your second condition as well.
      Feel free to check out our article about the IF function to learn why it is used and when in Excel.

      • Adam B says:


        I have a similar formula, but needs to look at two cells to see if they have values or not. basically I want to do this in AZ9:

        IF(isblank(R9) and isblank(AP9),"",sum(AP9,-(R9))

        If both R9 and AP9 are blank (they both included formulas) then AZ9 returns blank(""). If one of them has a number i need to take AP9 minus R9.

  52. Jaya says:

    Column I (Bridge #)
    Column M ( Grade)
    Column P (Qty)
    Actually, I need
    need to use 3 column 2 criteria

  53. Dan says:


    In your original example, if one was wanting to find out how many apples were not sold by Pete, is there a way to do this without telling the formula to calculate Mike and John and Sally's contributions?
    I.E. minus Pete (and any other multiples you want to find out).

    I have a spreadsheet where I need to calculate nationalities and I want to exclude only certain nationalities for my total. Is there a way to do this without having to input all of the nationalities I want into the formula?

    The SUMIFS with array formula works really well for the nationalities I now need to exclude from my final cells.


  54. Lauren Rider says:


    I was trying to do multiple if statements in one cell, when I try to enter the third if&sumif it states that I've entered too many arguments. Do you have any work around suggestions. Essentially I want the value in the cell to change based on what the user selects in the drop down box and the column that is calculated as the sum varies upon their selection.

    =IF($H$8="PO Amount",SUMIFS('01-2017'!AA:AA,'01-2017'!L:L,[@[More Info]],'01-2017'!C:C,[@[PO No.]]),IF($H$8="Open PO",SUMIFS('01-2017'!AG:AG,'01-2017'!L:L,[@[More Info]],'01-2017'!C:C,[@[PO No.]])))

  55. sathik says:


    The below function code will perfectly work in same sheet, i required multiple pages code, could you help on this.


  56. Tim McCutcheon says:

    I am trying to create a formula in a column with the range (f3:f27). I would like to count how many times, numbers between 0 and -150 are in that range? What should my formula be? =sumif(f3:f27,"-150") is not working. What am I missing?

  57. Gurutze says:


    I'm trying to sumifs from a dropdown list with this codes 3.1, 3.2 ... when it comes to 3.10 it takes the sum of 3.1 and not 3.10.


    Can someone help??

    Many thanks,


  58. Babu says:

    I am trying making the standard format to use the time sheet for technician,From this i need to calculate 2 separate time for all technician based on number of calls per day,one for Administration Time which is time used for Travelling in-between the customer & office and other one for time used at customer place,how much time Technician spent for that particular customer.See below format i used for my sheet & formula for the same.

    Office Time In (A) /In time @ Customer-1 Place(B) / Out time @ customer-1 Place(C) / In time @ Customer-2 Place(D) / Out time @ customer-2 Place(E) /In time @ Customer-3 Place(F) / Out time @ customer-3 Place(G).

    Formula : =((B16-A16)+(D16-C16)+(F16-E16))

    If i used above formula to calculate the Admin time,i am getting Minus Value if i not fill the time data@cell or if i use zero value for customer-3.

    Pls advice.

    Thanks & Regards


    • Trenton says:

      You search columns when using SUMIF/SUMIFS. If your data is in Rows, copy and paste transposed to move the data from rows to columns.

  59. Masud says:

    hi, tnx & can you plz. help me to get the formula for below..
    col:A Col:B Col:C Col:D
    product supplier qty date
    banna jony 10 1-Feb
    cherry moni 5 2-Jan
    apple toni 15 1-Feb
    pineaple koni 10 1-Mar
    banna jony 10 1-Feb
    banna jony 10 1-Jan
    cherry moni 5 2-Jan
    apple toni 15 1-Feb
    pineaple koni 10 1-Mar

    date :1-Feb
    result: 20

    plz. Sugest the formula (sumifs) to get the result: 20 , supplier=jony, product=appels, date=1-feb,
    Waiting for you kind help.


    • Trenton says:


      This is assuming you are looking at 2/1/2017. If not, enter the date you're looking for in Column D into an open cell, change the format to number, and replace the 42767 from the formula with the updated number.

      • Trenton says:

        Correction; damn dyslexia. Jony sold no apples on 2/1, according to the data. He did, however sell 20 banna on that date.

        • Hi Trenton,

          Thank you for sharing your knowledge on our blog!

          Just a minor improvement on the formula that can save a few seconds on date conversion:

          Excel SUMIF and SUMIFS can understand dates in the default format, so you can include the date directly in the formula like this:


          Or you can supply it by using the DATE function:

  60. Tim says:


    I need a formula that sums two numbers (cell a4+a5) if it is greater than 1 and less than 35. Also, I need the same for the difference of the same cells and criteria with absolute value. Lastly, for each formula where the sum or difference is outside the range, I need the cell to blank (no zeroes).

    Thank you for your assistance,

  61. Hammad says:


    I currently have a formula as =SUMIF(B3,"Buy",G3)*I3+J3+K3
    (If buy, multiply units by unit price, PLUS fee, PLUS tax).

    How do I add another argument, so that if B3 is “Divided”, “Distribution” , or “Sell”, then multiply units by unit price, LESS fee, LESS tax)?

    Many thanks


  62. Trenton says:

    Hello Ablebits team!

    I have an interesting one for you. Can we make the sum_range dependent upon criteria in another cell? Let's say I have this formula:


    But I want to be able to change the $AF based on a value in another cell (Let's assume cell D6), so that when I update the value in D6 to AG, the cell begins summing information in Column AG instead of Column AF. Is this possible with a formula, or do I have to update the sum_range columns every time I update?

    Большое спасибо!

  63. Darshqn says:


    This is Darshan.
    In example one. Can we mix with criteria rang also, as below.

    > Original >> SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],...)

    > Amended >> SUMIFS(sum_range, criteria_range1, criteria from A1:A20,[criteria_range2, criteria2],...)

    I hope this explain.

    Thank you,

  64. Amar says:

    if in criteria i used less than sign like <60 and if i want to get output from range who is matching with <60 then how i use sumifs formula to get that
    please find below example for more details on it,

    Age Amount
    <60 2500.00

    i m using formula as =sumifs(Range:criteria,criteria) so what is wrong in it

  65. Anshu says:


    I have a situation similar to "Example 2. SUM & SUMIF with an array argument" under "Using Excel SUMIF with multiple OR criteria".

    But, the array is specified in a different cell i.e. the criteria {"John","Mike","Pete"} is given in a different cell. How do I reference that cell with criteria in the sumif statement.


  66. Swapna says:


    I want to include "or" condition in sumifs in two different columns. How can that be done?
    Ex: Col1 Group : "Primary","Seconday" and Col2 Subjects : "Maths", "Science", "History".

    • Swapna says:

      Name Group Subject Marks
      Ashna Primary English 50
      Ashna Primary Maths 70
      Ashna Primary Science 45
      Ashna Primary History 55
      Ashna Secondary English 75
      Ashna Secondary Maths 68
      Ashna Secondary Science 89
      Ashna Secondary History 98
      Kirti University Science 56
      Kirti University Spain 63
      Kirti University History 52
      Kirti Secondary Science 87
      Kirti Secondary History 36
      Kirti Secondary Maths 49
      Archana Secondary History 126
      Mansi Primary Maths 89
      Sarika Secondary Science 90

      Out of this, for every name, marks should be added where group is Primary / Secondary and Subject Maths / Science /History

  67. pawan says:


    I want to total of three sum range using below formula in sumif function.Please suggest. I do not want to use three formula of Sumif+Sumif+Sumif.


    where D7:D18 is range , D4 is criteria and E7:G18 is sum range.

  68. Anand J says:


    I want to use the SUMIFS function for the forllowing table

    Column A has name of Customers Column B, C & D has year 2014, 2015 & 2016

    Sheet 2
    Column A Customer Name
    Column B Year
    Column C Months
    Column D Revenue from Customer - Product A
    Column E Revenue from Customer - Product B
    Column F Revenue from Customer - Product C
    Column G Revenue from Customer - Product D
    Column H Revenue from Customer - Product E

    I prepared the table in sheet 1 as mentioned above, but with sumifs i can create only one table for revenue from customer - product A

    If I want to change the table showing result as 'Revenue from Customer Product - B' I need to change he the details of sum range in sumifs formula.

    Can you give me a formula or a logic with whcih I can just change the reference formula get changed as per the reference in that cell.

    Thanks in Advance.
    Anand J

  69. Erica says:

    Please assist me

    i have a few conditions, before i want my calculations

    if Column A equals Cell A1 and if Column D equals Cell D1, then calculate Column E

    But my formula gives me #Value

    Forumula used and a couple of other that doesn't want to work

    =SUMIF(A:A,A1 & sumif D:D,D1,G:G) ive tried and tried

    Please please help

  70. Artour says:

    Hello i have a question.

    I was looking for a function that add votes or number based on criteria. for example in range from A1:A10 for every "Artour" will add +1 to box C3, so if i have 1 Artour C3=1, and if i have 5 it will be 5. I was thinking perhaps sumif was the right function but apparently i was wrong. I could really use some help.

    thanks before

    • Hi, Artour,
      the function you need is called COUNTIF. For your given example, place the following into C3 cell:

      Note, that it won’t count the word “Artour” within a cell with more than one word (meaning, it works only for the cell with one single word “Artour” in it). For more details and info, please, feel free to check another article out - COUNTIF in Excel.

  71. Sharon says:

    I am having trouble with text. My formula is =SUMIFS(YTD!$H:$H,YTD!$E:$E,">=1/1/2017",YTD!$E:$E,"<=1/31/2017",YTD!$G:$G,"Jason")
    I want to know how many deals Jason did in January, but it's only picking up some of them, and not every time his name comes up. Any tips?

  72. Mubashar Rafiq says:

    in sumif formula =SUMIF(ZDDR!$G$5:$G$1456,A5,ZDDR!$H$5:$H$1456)
    it automatically calculates what is in A5 matches with the ranges

    but when i use sumifs formula


    it will not give any answer. My problem is i want to use cell number insted of text how is it possible.

  73. Dale says:

    Hi Svetlana, could i please get some help with this one

    My previous formula is working, but we have had a change of data from another department, so i am hoping to get an outcome from the following

    Column AF has a total figure of $, in that total, they are either amounts of $135 or $180, but the spreadsheet that i am importing into my spreadsheet doesn't specify that amount that the total is derived from.

    my previous formula was

    =SUM(SUMIFS(AF11:AF5000,I11:I5000,{"BSB30115"},AK11:AK5000,{"organisation name"}))

    I would like two formulas as they need to go into two separate cells, so from Column AF, i need a formula that uses that column and works out the total amount, whether it is 2 amounts of $360 (these amounts can be as high as $2520 in total), 1 amount of $180 and then the other one being the same type of formula but the amount of $135.

    so the return values would be in two cells, one being for the $180 amounts (total) and then one for $135 amounts (total)

    so my end result would be splitting the one column to have two different totals for each of the criteria, hope this makes sense,

    Thank you in advance

  74. Jimmy says:

    the formula that I am using is not showing up in the previous 2 posts, when I try copy and paste the formula. SUMIFS($Q$2:$Q$3208, $F$2:$F$3208, $R20, $G$2:$G$3208, ">=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

  75. Jimmy says:

    This is driving me nuts. Any help is appreciated. I wish to sum numbers in Col Q, if text in Col F matches matches text value in R20 AND dates in Col G >=9/1/2015 and Col H=9/1/2015", $H$2:$H$3208, "=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

    Thank you,

  76. Jimmy says:

    This is driving me nuts. Any help is appreciated. I wish to sum numbers in Col Q, if text in Col F matches matches text value in R20 AND dates in Col G >=9/1/2015 and Col H=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

    I need to sum

  77. Navas Khan says:

    Dear Experts,

    Kindly help below mentioned query...use sumif function - account code is unique data, but i need sum if month should be matching ....

    Month AccountCode Amount
    Aug-16 21011077 4290
    Sep-16 21011077 4070
    Oct-16 21011077 5500
    Nov-16 21011077 5500
    Dec-16 21011077 4730
    Jan-17 21011077 5230
    Feb-17 21011077 5990

    A.Code Description Aug-16 Sep-16
    21011077 Visa Letter Fee

    Unique Data is Account code, but sum is arrive based on month.

    Thanks in Advance...
    Ahmed Khan

    • Christian says:

      Dear Ahmed,

      You can try this formula.


      Hope this helps.

  78. Kylie Keenan says:

    I need a formula for a profit and loss sheet I am creating. I need to add a column of cells but only those that are listed as a specific month ie 'January', 'Feb' etc.
    Then I need these January amounts to be bought back into another sheet and for them to match in the corresponding cell.

    Example Sheet 1 'Expenses Entry'-
    Column heading in Column B'Month'- January/Feb/ April/ Dec etc
    Column heading in Column D 'Description'- Bank Charges/ Food/ Tools etc
    Column Heading in Column F 'Amount'- $40.00/ $55.00 etc ( these are the amounts I need to be added up if they all fall under January, then I wil copy the formula for Feb/ March/ April etc

    Sheet 2- 'Profit and Loss
    Column A is for all the descriptions- Bank Charges/Food/Tools etc
    Column C is where I want the sum of all January's Bank Charges to be or whatever the name of the 'Description' is

  79. Konstantinos says:

    In "Example 2. SUM & SUMIF with an array argument", there is the following combination of functions

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

    Instead of writing "John","Mike",etc., can we use the cells that they are into? For example,

    =SUM(SUMIF(C2:C9, {G3,G2,G4} , D2:D9))

    even though the above it's wrong

  80. VIJAY KUMAR says:

    Can someone help in solving this as this is killing my machine. Some simple formula to run sheet faster

    =SUMIFS('Same Day TAT'!$Q:$Q,'Same Day TAT'!$H:$H,Summary!$D9,'Same Day TAT'!$C:$C,Summary!$B$2,'Same Day TAT'!$D:$D,Summary!$B$3,'Same Day TAT'!$E:$E,Summary!$B$4,'Same Day TAT'!$F:$F,Summary!$B$5,'Same Day TAT'!$G:$G,Summary!$B$6,'Same Day TAT'!$L:$L,Summary!F$8)

  81. Nilesh says:

    I have two sheets , in one sheet there is only one column enlisted certain items and in second sheet there are two columns. In one column there is list of item & in second column, there is list of values corresponding to items in first column.
    Sheet 1
    Part No
    Sheet 2
    Engine Qty
    A 10
    B 10
    C 20
    D 20
    E 25
    A 15

    I want to run sum if in sheet two only for common items between first column in sheet 1 & sheet 2
    please guide

  82. Bijendra Sah says:

    Respected Sir,

    i want to create some formula for my office use but i am unable so will you please help me?

    if C25= 6,00,000
    then E29 can calculate 1% of C25 till 350000
    Thne E30 can calulate 15% of C25 till +100000
    and last E31 calculate 25 % of C25 balance amount (150000)

  83. SAMAD says:

    i need to devolop sum if for tow condition

    sales incentive preapartin
    eligilbe only if sales (1) and collection shoulbe achived

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