How to use SUMIF in Excel - formula examples to conditionally sum cells

This tutorial explains the Excel SUMIF function in plain English and provides a numbers of SUMIF formula examples for numbers, text, dates and wildcards.

If you are faced with the task that requires conditional sum in Excel, the SUMIF function is what you need. This tutorial will briefly explain the function's syntax and general usage, and then we will apply the new knowledge in practice by making a few SUMIF formula examples.

A good thing is that the SUMIF function is identical in all Excel versions, from 2016 to 2003. Another great news is that once you've invested some time in learning SUMIF, it will take you very little effort to get the insight of other "IF" functions such as SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF etc.

SUMIF in Excel - syntax and usage

The SUMIF function, also known as Excel conditional sum, is used to add cells based on a certain condition, or criteria.

If you've happened to read the COUNTIF tutorial on this blog, you won't have any difficulties with understanding Excel SUMIF because its syntax and usage is analogous. So, the syntax of the SUMIF function is as follows:

SUMIF(range, criteria, [sum_range])

As you see, the SUMIF function has 3 arguments - first 2 are required and the 3rd one is optional.

  1. range - the range of cells to be evaluated by your criteria, for example A1:A10.
  2. criteria - the condition that must be met. The criteria may be supplied in the form of a number, text, date, logical expression, a cell reference, or another Excel function. For example, you can enter the criteria such as "5", "cherries", "10/25/2014", "<5", "A1", etc.
    Note. Please pay attention that any text criteria or criteria containing mathematical symbols must be enclosed in double quotation marks ("). For numerical criteria, double quotation marks are not required.
  3. sum_range - the cells to sum if the condition is met. This argument is optional, and you need to use it only if you want to sum cells other than defined in the range argument. If the sum_range argument is omitted, Excel will sum the same cells to which the criteria is applied (i.e. specified in the range argument).

    To illustrate the Excel SUMIF syntax better, let's consider the following example. Suppose you have a list of products in column A and corresponding amounts in column C. You want to know the sum of all amounts relating to a given product, e.g. bananas.
    Data to be conditionally summed.

    Now, let's define the arguments for our SUMIF formula:

    • range: A2:A8
    • criteria: "bananas"
    • sum_range: C2:C8

    Putting this together, you get: =SUMIF(A2:A8, "bananas", C2:C8)

    Excel SUMIF formula example

    This formula example demonstrates the simplest usage of the SUMIF function with the text criteria. Instead of text, you can include a number, date or a cell references in your criteria. For instance, you can re-write the above formula so that it references the cell containing the name of the product to be summed:

    =SUMIF(A2:A8,F1,C2:C8)

    Note. The sum_range parameter actually specifies only the upper leftmost cell of the range to be summed. The remaining area is defined by the dimensions of the range argument.

In practice, this means that the sum_range argument does not necessarily have to be of the same size as the range argument, i. e. it may have a different number of rows and columns. However, the first cell (i.e. the top left cell) of the sum range must always be the right one. For example, in the above SUMIF formula, you can supply C2, or C2:C4, or even C2:C100 as the sum_range argument, and the result will still be the same. However, the best practice is to provide equally sized range and sum_range.

The point is that Microsoft Excel does not rely on the user's ability to provide matching range and sum_range parameters, so to avoid possible inconsistency issues and prevent errors, it determines the sum range on its own in the following way. It takes the top left cell in the sum_range argument as the beginning cell (C2 in our Excel SUMIF example), and then includes as many columns and rows as contained in the range argument (in our case, it's 1 column x 7 rows, i.e. C2:C8).

How to use SUMIF in Excel - formula examples

Hopefully, the above example has helped you to gain some basic understanding of how the SUMIF function works. Below you will find a few more formulas that demonstrate how to use SUMIF in Excel with various criteria and on different data sets.

SUMIF greater than, less than or equal to

Let's have a look at a few SUMIF formulas that you can use to add up values greater than, less than or equal to a given value.

Note. Please pay attention that in Excel SUMIF formulas, a comparison operator followed by a number or text should always be enclosed in double quotes ("").
Criteria Operator Formula Example Description
Sum if greater than > =SUMIF(A2:A10, ">5") Sum the values over 5 in cells A2:A10.
Sum if less than < =SUMIF(A2:A10, "<10", B2:B10) Sum the values in cells B2:B10 if a corresponding value in column A is less than 10.
Sum if equal to =
(can be omitted)
=SUMIF(A2:A10, "="&D1)
or
=SUMIF(A2:A10,D1)
Sum the values in cells A2:A10 that are equal to the value in cell D1.
Sum if not equal to <> =SUMIF(A2:A10, "<>"&D1, B2:B10) Sum the values in cells B2:B10 if a corresponding cell in column A is not equal to the value in cell D1.
Sum if greater than or equal to >= =SUMIF(A2:A10, ">=5") Sum the values greater than or equal to 5 in the range A2:A10.
Sum if less than or equal to <= =SUMIF(A2:A10, "<=10", B2:B10) Sum the values in cells B2:B10 if a corresponding value in column A is less than or equal to 10.

How to use Excel SUMIF with text criteria

Apart from numbers, the SUMIF function enables you to add values depending on whether a corresponding cell in another column contains a given text or not.

Please pay attention that you will need different SUMIF formulas for exact and partial match, as demonstrated in the table below.

Criteria Formula Example Description
Sum if equal to Exact match:
=SUMIF(A2:A8, "bananas", C2:C8)
Sum values in cells C2:C8 if a corresponding cell in column A contains exactly the word "bananas" and no other words or characters. Cells containing "green bananas", "bananas green" or "bananas!" are not included.
Partial match:
=SUMIF(A2:A8, "*bananas*", C2:C8)
Sum values in cells C2:C8 if a corresponding cell in column A contains the word "bananas", alone or in combination with any other words. Cells containing "green bananas" or "bananas green" are summed.
Sum if not equal to Exact match:
=SUMIF(A2:A8, " <>bananas", C2:C8)
Sum values in cells C2:C8 if a corresponding cell in column A contains any value other than "bananas". If a cell contains "bananas" together with some other words or characters like "yellow bananas" or "bananas yellow", such cells are summed.
Partial match:
=SUMIF(A2:A8, " <>*bananas*", C2:C8)
Sum values in cells C2:C8 if a corresponding cell in column A does not contain the word "bananas", alone or in combination with any other words. Cells containing "yellow bananas" or "bananas yellow" are not summed.

For more information about partial match, please see SUMIF examples with wildcard characters.

And now, let's see the exact "Sum if not equal to" formula in action. As illustrated in the screenshot below, it adds in-stock amounts of all products other than "goldfinger bananas":

=SUMIF(A2:A7,"<> goldfinger bananas", C2:C7)
'Sum if not equal to' formula example

Note. Like most of other Excel functions, SUMIF is case-insensitive, meaning that "<>bananas", "<>Bananas" and "<>BANANAS" will produce exactly the same result.

Using comparison operators with cell references

If you want to take a step further and get a more universal Excel SUMIF formula, you can replace a numerical or text value in the criteria with a cell reference, like this:
=SUMIF(A2:A8,"<>"&F1, C2:C8)

In this case, you won't have to change the formula to conditionally sum values based on another criteria - you will simply type a new value in a referenced cell.
Sum in-stock amounts of all products other than in cell F1.

Note. When you use a logical expression with a cell reference, you must use the double quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off, for example "<>"&F1.

The "equal to" operator (=) can be omitted before a cell reference, therefore both of the below formulas are equivalent and correct:

Formula 1: =SUMIF(A2:A8, "="&F1, C2:C8)

Formula 2: =SUMIF(A2:A8, F1, C2:C8)

SUMIF formulas with wildcard characters

If you aim to conditionally sum cells based on a "text" criteria and you want to sum by partial match, then you need to use wildcard characters in your SUMIF formula.

The following wildcards are available to us:

  • Asterisk (*) - represents any number of characters
  • Question mark (?) - represents a single character in a specific position

Example 1. Sum values based on partial match

Suppose, you want to sum amounts relating to all sorts of bananas. The following SUMIF formulas will work a treat:

=SUMIF(A2:A8, "*bananas*", C2:C8) - the criteria includes text enclosed in asterisks (*).

=SUMIF(A2:A8, "*"&F1&"*", C2:C8) - the criteria includes a cell reference enclosed in asterisks, please notice the use of ampersand (&) before and after a cell reference to concatenate a string.
Using wildcards to sum values based on a partial match

If you want to count only those cells that start or end with certain text, add just one * either before or after the text:

=SUMIF(A2:A8, "bananas*", C2:C8) - sum values in C2:C8 if a corresponding cell in column A begins with the word "bananas".

=SUMIF(A2:A8, "*bananas", C2:C8) - sum values in C2:C8 if a corresponding cell in column A ends with the word "bananas".

Tip. To use wildcards with cell references, combine them with the help of the concatenation operator (&). In this example, you could also use the following formula to sum all "bananas" in stock: =SUMIF(A2:A8, "*"&F1&"*", C2:C8)

Example 2. Sum values with a given number of characters

In case you want to sum some values that are exactly 6 letters long, you would use the following formula:

=SUMIF(A2:A8, "??????", C2:C8)

Example 3. Sum cells corresponding to text values

If your worksheet contains different data types and you only want to sum cells corresponding to text values, the following SUMIF formulas will come in handy:

=SUMIF(A2:A8,"?*", C2:C8) - adds up values in cells C2:C8 if a corresponding cell in column A contains at least 1 symbol.

=SUMIF(A2:A8,"*",C2:C8) - includes seemingly empty cells that contain zero length strings returned by some other formulas, e.g. ="".

Both of the above formulas ignore non-text values such as errors, booleans, numbers and dates.

Example 4. Treat * or ? as usual characters

If you want either * or ? to be treated as a literal rather than a wildcard, then use the preceding tilde (~). For example, the following SUMIF formula will add values in cells C2:C8 if a cell in column A in the same row contains a question mark:

=SUMIF(A2:A8, "~?", C2:C8)
A SUMIF formula adds values corresponding to the question mark in another column

Sum the largest or smallest numbers in a range

To add the largest or smallest numbers in the range, use the SUM function together with the LARGE or SMALL function, respectively.

Example 1. Add a few largest / smallest numbers

If you want to sum just a few numbers, say 5, you can type them directly in the formula, like this:

=SUM(LARGE(B1:B10,{1,2,3,4,5})) - sum the largest 5 numbers

=SUM(SMALL(B1:B10,{1,2,3,4,5})) - sum the smallest 5 numbers
Sum the largest and smallest numbers in a range

Note. If there are 2 or more numbers that are tied for last place, only the first will be included. As you see, the second 9 is not added up in the above example.

Example 2. Add many top / bottom numbers

If you want to add up many numbers, instead of enumerating them all in the formula, you can nest the ROW and INDIRECT functions in your SUM formula. In the INDIRECT function, use row numbers that represent the number of values you want to add up. For example, the following formulas sum the top and bottom 15 numbers, respectively:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:15"))))

=SUM(SMALL(B1:B50,ROW(INDIRECT("1:15"))))

Since these are array formulas, remember to enter them in the array-way by pressing Ctrl + Shift + Enter.
Array formulas to add many top / bottom numbers

Example 3. Sum a variable number of largest / smallest values

If you'd rather not change the formula every time you want to sum a different number of cells, you can put that number in some cell instead of entering it directly in the formula. And then, you can use the ROW and INDIRECT functions as demonstrated above and refer to a cell containing the variable, cell E1 in our case:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1)))) - sums a variable number of top values

=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1)))) - sums a variable number of bottom values

Sum a variable number of largest / smallest values by referencing a cell

Remember, these are array formulas, so you have to press Ctrl + Shift + Enter to complete them.

How to sum cells that correspond to blank cells

If "blank" means cells that contain absolutely nothing - no formula, no zero length string returned by some other Excel function, then use "=" as the criteria, like in the following SUMIF formula:

=SUMIF(A2:A10,"=",C2:C10)

If "blank" includes zero length strings (for example, cells with a formula like =""), then use "" as the criteria:

=SUMIF(A2:A10,"",C2:C10)

Both of the above formulas evaluate cells in column A and if any empty cells are found, the corresponding values from column C are added.
Summing cells that correspond to blank cells

How to sum cells corresponding to non-blank cells

If you want to sum cells' values in column C when a corresponding cell in column A is not empty, use "<>" as the criteria in your SUMIF formula:

=SUMIF(A2:A10,"<>",C2:C10)

The above formula counts values corresponding to all non-empty cells, including zero length strings.

How to use Excel SUMIF with dates

Generally, you use the SUMIF function to conditionally sum values based on dates in the same way as you use text and numeric criteria.

If you want to sum values corresponding to the dates that are greater than, less than or equal to the date you specify, then use the comparison operators that we've discussed a moment ago. A few Excel SUMIF formula examples follow below:

Criteria Formula Example Description
Sum values based on a certain date. =SUMIF(B2:B9,"10/29/2014",C2:C9) Sum values in cells C2:C9 if a corresponding date in column B is 29-Oct-2014.
Sum values if a corresponding date is greater than or equal to a given date. =SUMIF(B2:B9,">=10/29/2014",C2:C9) Sum values in cells C2:C9 if a corresponding date in column B is greater than or equal to 29-Oct-2014.
Sum values if a corresponding date is greater than a date in another cell. =SUMIF(B2:B9,">"&F1,C2:C9) Sum values in cells C2:C9 if a corresponding date in column B is greater than the date in F1.

In case you want to sum values based on a current date, then you have to use Excel SUMIF in combination with the TODAY() function as demonstrated below:

Criteria Formula Example
Sum values based on the current date. =SUMIF(B2:B9, TODAY(), C2:C9)
Sum values corresponding to a prior date, i.e. before today. =SUMIF(B2:B9, "<"&TODAY(), C2:C9)
Sum values corresponding to a future date, i.e. after today. =SUMIF(B2:B9, ">"&TODAY(), C2:C9)
Sum values if a date occurs in a week (i.e. today + 7 days). =SUMIF(B2:B9, "="&TODAY()+7, C2:C9)

The screenshot below illustrates how you can use the last formula to find the total amount of all products that ship in a week.
Sum values corresponding to the date that occurs in a week

How to sum values in a given date range

The following question is frequently asked on Excel forums and blogs - "How do I sum between two dates?"

The answer is to use a combination, or more precisely, the difference of 2 SUMIF functions. In Excel 2007 and higher, you can also use the SUMIFS function that allows multiple criteria, which is even a better option. While the latter is the subject of our next article, an example of the SUMIF formula follows below:

=SUMIF(B2:B9, ">=10/1/2014", C2:C9) - SUMIF(B2:B9, ">=11/1/2014", C2:C9)

This formula sums up the values in cells C2:C9 if a date in column B is between 1-Oct-2014 and 31-Oct-2014, inclusive.
SUMIF formula to add values in a given date range

This formula might seem a bit tricky at first sight, but upon a closer look, it appears quite simple. The first SUMIF function adds up all the cells in C2:C9 where the corresponding cell in column B is greater than or equal to the start date (Oct-1 in this example). Then you just have to subtract any values that fall after the end date (Oct-31), which are returned by the second SUMIF function.

How to sum values in several columns

To understand the problem better, let's consider the following example. Suppose, you have a summary table of monthly sales. Since it was consolidated from a numbers of regional reposts, there are a few records for the same product:
Values in several columns need to be conditionally summed.

So, how do you find the total of apples sold in all the states in the past three months?

As you remember, the dimensions of sum_range are determined by the dimensions of the range parameter. That is why you cannot use the formula like =SUMIF(A2:A9, "apples", C2:E9) because it will add up the values corresponding to "Apples" in column C only. This is not what we are looking for, right?

The most logical and simplest solution that suggests itself is to create a helper column that calculates individual sub-totals for each row and then reference that column in the sum_range criteria.

Go ahead and place a simple SUM formula in cell F2, then fill down column F: =SUM(C2:E2)

After that, you can write a usual SUMIF formula like this:

=SUMIF(A2:A9, "apples", F2:F9)
or
=SUMIF(A2:A9, H1, F2:F9)

In the above formulas, sum_range is exactly of the same size as range, i.e. 1 column and 8 rows, and therefore they return the correct result:
Solution to sum values in several columns

If you'd rather do without a helper column, then you can write a separate SUMIF formula for each of the columns you want to sum, and then add the returned numbers using the SUM function:

=SUM(SUMIF(A2:A9,I1,C2:C9), SUMIF(A2:A9,I1,D2:D9), SUMIF(A2:A9,I1,E2:E9))

Another way is using a more complex array formula (don't forget to press Ctrl + Shift + Enter):
{=SUM((C2:C9+D2:D9+E2:E9)*(--(A2:A9=I1)))}

Both of the above formulas will return 2070 in our case.

Why my SUMIF formula is not working?

There could be several reasons why Excel SUMIF is not working for you. Sometimes, your formula does not return what you expect only because the data type in a cell or in some argument isn't suited for the SUMIF function. So, here is a list of things to check.

1. The range and sum_range parameters should be ranges, not arrays

The first (range) and third (sum_range) parameters of your SUMIF formula must always be a range reference like A1:A10. If you try to pass in anything else, for example an array like {1,2,3}, Excel with throw an error message.

Correct formula: =SUMIF(A1:A3, "flower", C1:C3)

Wrong formula: =SUMIF({1,2,3}, "flower", C1:C3)

2. How to sum values from other sheets or workbooks

As almost any other Excel function, SUMIF can reference other sheets and workbooks, provided they are currently open.

For example, the following formula will sum the values in cells F2:F9 in Sheet 1 of Book 1 if a corresponding cell in column A if the same sheet contains "apples":

=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,"apples",[Book1.xlsx]Sheet1!$F$2:$F$9)

However, this formula won't work as soon as Book 1 is closed. This happens because the ranges referenced by SUMIF formulas in closed workbooks get de-referenced into arrays, and since no arrays are allowed in the range and sum_range arguments, a SUMIF formula will throw a #VALUE! error.

3. To avoid problems, make sure range and sum_range are of the same size

As noted in the beginning of this tutorial, in modern versions of Microsoft Excel, the range and sum_range parameters does not have to be equally sized. In Excel 2000 and older, unequally sized range and sum_range can cause problems. However, even in the most recent versions of Excel 2010 and Excel 2016, complex SUMIF formulas where sum_range has less rows and/or columns than range are capricious. That is why it's considered a good practice to always have the range and sum_range arguments of the same size and shape.

4. How to make your SUMIF formulas work faster

If you've populated your workbook with complex SUMIF formulas that slow down your Excel, check out this article and learn how to make them work faster. The article was written quite long ago, so don't be surprised by the calculation time. Their recommended approaches and formula examples are still actual and brilliant!

The Excel SUMIF examples described in this tutorial only touch on some of the basic usages of this function. In the next article, we'll investigate advanced formulas that harness the real power of SUMIF and SUMIFS and let you sum by multiple criteria. Please stay tuned and thank you for reading!

You may also be interested in:

237 Responses to "How to use SUMIF in Excel - formula examples to conditionally sum cells"

  1. manish says:

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

  2. Joe Sephons says:

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

  3. Caroline Stewart says:

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

    • Hi Caroline,

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

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

  4. George Luke says:

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

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

  5. John says:

    Hi

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

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

    Many thanks John

  6. John says:

    Sorry that should look like this

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

    sheet two
    apple 2 green jones
    apple 4 red smith

  7. Clare Cooper says:

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

  8. Obaydul says:

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

    • Hi Obaydul,

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

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

      • Manju says:

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

  9. Antti says:

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

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

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

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

    What am I doing wrong?

  10. Bob Duff says:

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

  11. Charlie says:

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

  12. Sandy says:

    Wonderful blog!

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

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

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

    Thank you!

    • Hello Sandy,

      Thank you very much for your feedback!

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

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

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

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

  13. Bella says:

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

    Basically I have this on Sheet1:

    Account Number
    ABC

    Qty Stock Code Price
    123 $1
    456 $2

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

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

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

    Thanks so much!

    • Hello Bella,

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

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

      Please see this blog post for more details.

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

  14. Ben says:

    Hi, great blog.

    I have a challenging problem:

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

    e.g.

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

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

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

    Can you help?
    Hi, great blog.

    I have a challan

    • Hi Ben,

      Thank you very much for your feedback.

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

  15. faisal azeem says:

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

  16. jackie says:

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

  17. Cecilia says:

    Thanks for time range formula! It worked.

  18. MURUGAVENDAN says:

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

  19. MICHAEL says:

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

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

    THANKS FOR THE HELP

    • Hello Michael,

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

  20. Thany says:

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

    Regards

    • Hello Thany,

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

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

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

  21. Grant says:

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

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

    Thanks!

  22. brian says:

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

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

    total for 2015 should be 125.00

    • Hello Brian,

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

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

  23. Mac says:

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

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

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

    • Alexander says:

      Hello Mac,

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

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

  24. TOMEU says:

    Hello

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

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

    Thank you

    • Hello Tomeu,

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

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

  25. John says:

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

  26. Tonis says:

    Hi,
    This is really very much helpful.

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

    Thanking You...
    Tonis

    • Hello Tonis,

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

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

  27. José says:

    Hi,
    Using your given example.

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

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

    Thanks in advance.
    Best regards,
    José

    • Hi Jose,

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

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

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

      - Click Run.

      You can see a sample spreadsheet with this solution here.

  28. J.Alvi says:

    I am working on a spreadsheet that tallies up donations (cans) per day but I want to have one cell tally for every 10 cans donated they receive 1 pt.

    What formula should I use?

    Thanks.

  29. J.Alvi says:

    I got it... thanks.

  30. Awali Bukar says:

    Am very grateful 1,000 000 thanks...

  31. Shawnda says:

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

  32. Dub Smith says:

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

  33. Greg says:

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

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

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

    • Hi Greg,

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

      • Greg says:

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

        Thanks again!

  34. Deepak says:

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

  35. Lory says:

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

    Col A
    36
    0
    253

    Col B
    2
    0
    3

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

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

    Thank you in advance.

  36. Ghazi says:

    Hi,
    I want to look for any cell that contains number in celles range and sum thos numbers?

  37. Ghazi says:

    Hi,
    I need an Excel VBA function to look for numbers in range of cells that contain both numbers and text and sum those numbers.

  38. Rupert says:

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

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

    I am sure this can be done - but how?

    Best wishes

    RJ

  39. Spencer G says:

    I am trying to match amounts from two different datasets. I have cell A1(GL acct #) B1(trial balance $ amt) and then from an entirely different system with the exact same format A1(GL acct #) B1(trial balance $ amt). These GL accts are mapped to one another, the issue with my sumif is that it will pull the return the same $ for multiple lines. This is because in some cases 4 different GL accts from system 1 map into only 1 GL acct from system two.

    I want my sumif to only return the amount ONCE, as opposed to every time it comes across a match. A nested sumif is my thought, saying something like IF((___ has not yet appeared)then(sumif(yadayadayada))else(return(n/a)) but i cant figure it out.

  40. Rasika says:

    Hi,

    Reg SUMIFS formula.

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

  41. Dianne says:

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

    Example:

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

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

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

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

  42. Rebecca says:

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

  43. Sarah says:

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

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

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

  44. beth says:

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

  45. Sean says:

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

  46. Rae says:

    Hi,

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

    Training Date

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

  47. Shaz says:

    I have an Excel 2010 worksheet containing a separate "$ amount" column for each of 3 entities (column headers: MD, MDM & MDW).
    The data is rows of direct debit amounts from the company's bank accounts that are specified in 3 "Frequency"-header columns ("MTHLY", "QTRLY" or "ANNUALLY").
    I have auto-summed each entity column, so have an overall direct debit total for each of the 3 entities (MD, MDM & MDW).
    However, I need to auto-calculate the total - OF ALL 3 ENTITIES TOGETHER - for each of the 3 frequency columns (so $ amount total for "monthly", "quarterly" & "annual" direct debits)
    I have wasted the last couple of hours trying to find a formula for this & have tried a variety of SUMIF cell combinations/formulas, but cannot get this to work!!!!
    Can you please advise?

  48. Vikash Srivastava says:

    1 Vikash 50 ?
    50
    50
    50
    2 Mahesh 100 ?
    100
    100
    100
    3 Rakesh 500 ?
    500
    500
    I want to sum these values where is the question Mark (?) in excel with single formula.Please help me.

  49. hazel says:

    i just want to sum all in one merge cell only
    Thanks You;

  50. bismark says:

    Hi?

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

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

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

    Thanks in advance

    Bismark

    • Hello Bismark,

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

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

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

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

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

  51. Anonymous says:

    How to sort data when cell are merged and wraped

  52. Justin says:

    Hi, Svetlana,

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

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

    Even better formula would be:

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

    I can't get correct formula anyhow...

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

    • Hello Justin,

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

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

  53. Diana says:

    I am trying to create a sumif of sumifs formula using named ranges and using either a >0 or 0",SUMIF(BSMap_to,"Interco",BSJun_Act)0)

    IF(Sumif(BSMap_to,"Interco")>0,SUMIF(BSMap_to,"Interco",BSJun_Act))

    It seems to work if I use an actual range like A15:A27, but I can't seem to find the answer to get this formula to work using named ranges where the range is not in a series.

    Thanks for your help!

  54. Diana says:

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

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

    sum range = BSJun_Act
    criteria = BSMap_to,"Interco"

    • Hello Diana,

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

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

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

  55. Jeannie says:

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

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

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

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

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

    Can you help?

    • Jeannie says:

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

      Jeannie

    • Hello Jeannie,

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

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

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

  56. Hari Mohan Singh says:

    Hello,
    Can you help me regarding sumif formula which not working properly.
    Name of DAy form Sunday to Saturday in Column A and number of working hours in column B ( and column B all values are base on IF formula ).
    When I want to apply SUMIF formula its not working because my target hours base on if formula in column B.

    Same formula when I apply in a simple way then its working properly.

  57. Hari Mohan Singh says:

    Hello,

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

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

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

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

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

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

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

    Thanks if anyone can give anwser

  58. Jeannie says:

    I don't think this site is being monitored anymore. I don't see any answers.

    Hello?

  59. michael says:

    I am trying to create a formula that puts in a cell an invoice amount based on different date ranges.
    The invoice amount will be $1000 if the invoice date is between 2 date ranges in a month say August (100 %)
    The invoice amount will then drop to 900 if between 2 date ranges in September(90%)
    The invoice amount drops to say $800 if between 2 date ranges in October.

  60. Eva says:

    Hi

    I'm trying to set up a prepayments schedule and I have the start date, end date, amount and daily rate shown is separate cells for an invoice. I have 12 further columns for each month of the year. I would like to have the invoice amount split between the months according to the number of days relevant to the month. eg an invoice may run from 13th of 1 month to the 12 of another. is there a formula that will calculate the number of days relevant for the month and multiply this by the daily rate?

  61. Joseph says:

    Hi
    I am needing a formula that sums only if both cells have a value.
    And that answer will be multiplied by another cell
    For example =a25+b25*g28
    The formula is put into c28, I don't want anything appearing in c28 until both a25 and b25 contain a number or value, It needs to wait to add until I put a number into both cells,
    Thank you soo much for your time,
    I am useing 2013 excel

  62. ZVI GENATI says:

    hi
    i have row with the following data in the cells:
    A3=20, B3=50, C3=PAID, D3=60, E3=PAID, F3=60, G3=PAID, H3=20, I3=40, J3=60
    I WANT TO SUM THE FOLLOWING CELLS: A3, C3, E3, F3, J3
    AS A RESULT I GET AN ERROR NAMED "VALUE".
    HOW CAN I SUM THESE CELLS WITHOUT GETTING AN ERROR.
    THANK YOU,
    ZVI

  63. Ankush Jain says:

    I am trying to work on a sheet where i have customer sales details of two products from which i want to calculate which customer has purchased what product in total. for one i cn calculate using "sumif" formula but its difficult to change the for formula for each customer.. can you please suggest me

  64. Tanya says:

    Hello, I cam trying to create a formula where I have 2 cells - one is the gross weight and the other is the dimensional weight. sometimes it differs on which one is greater. I need to create a sum of multiplying the charge by which ever the weights is greater. Can you please help me?

  65. dev says:

    I wish to set up a criteria in such a way that it adds numbers in a range but the criteria excludes certains conditions within the criteria range.

    Row A B
    1 212200 10
    2 218000 20
    3 214000 40
    4 215000 50
    5 216000 60
    6 217000 80

    I this example I want to add up column b(b1:b6), criteria range (A1:A6), criteria is anything => 212200, but <=217000 and exclude 215000 to 216000.

    Please help...

  66. Lisa says:

    I have never commented on such a forum before. However, thought you deserved truck loads of positive feedback on a concise, easy to understand, educational post. Thank you.

  67. Timothy Fay says:

    First Example:
    A1 (Date); B1 (Bucket Size of 2.5 gallons); C1(Seconds To Fill Bucket); D1 (Gallons Per Minute).

    Quest:
    A1 (Date); B1 (Seconds To Fill Bucket);C1
    Gallons Per Minute

    Hi,
    I’m cyber (computer) un-savvy . . .
    I succeeded with three cells, in that, that equation =SUM(B1/C1*60) imposed in D1 will provide me with a correct answer. . Note, A1 is just the date of occurrence and is NOT necessary for the equation.
    My quest is to formulate the equation so that it only involves two cells. B1 represents the constant (2.5 gallon bucket) that is divided by a variable (time of seconds to fill the bucket) that is entered into the cell, and C1 is the hiddeb equation that displays the answer (gpm). In essence, the equation is 2.5/B1*60=gpm . . . 2.5 is a constant bucket size, B1 is the variable of time (seconds to fill the bucket), *60 is a constant (60 seconds) = ANSWER (gallons per minute)
    I have partial success, in that, C1’s equation =SUM(2.5/A1*60) functions if A1’s value is less or greater than 0 (zero). However, with the aforementioned equation, if the B1 value is 0 (zero) or left empty (implication that a sample was not obtained for that date) then there is an annoying and always present cell admonition of #DIV/0!.
    Can you help???
    Blessings,
    Tim

  68. marshell says:

    Hi my homework says "In cell B33, enter a formula to add the individual 5 cells one at a time." how do I go about doing that?

    • Timothy Fay says:

      Hi, I just happen to be on the site to see if there was an answer to my question.

      For you, the easiest way is just to click on B33, enter =SUM(B1:B5) into the formula space, click the check mark next to the fx icon.

      If you did it correctly then when you enter the values in cells B1 through B5 then B33 will add those independent values.

  69. Karem says:

    Hi,

    I want to use special character as >, 1 5
    >1 10
    |K 3
    |K 1

    Summary
    Codes Amount
    >1 15
    |K 4
    I need to use sumifs because i had multiple conditions in my table

    Thank

  70. Emma says:

    Hello,

    I need to use a SUMIF formula I am pretty sure.

    I have one sheet with outstanding invoices on it, and another sheet with a total for each customer as they aid I mark them YES for paid.
    I need to work out what the formula can be so the paid invoices are not included in the total for outstanding.
    Can anyone help?

  71. HarrisLyfe2016 says:

    Can someone PLEASE help me?

    I am trying to get a some of column A1:A1500 (only numerbic values) however, I have 2 additional conditions - I only want Excel to add the numerbic values in A1:A1500 for Nike's (type of sneakers) and the color Pink (color)

    F:F is where the different types of Sneakers are listed and and column H:H is the various colors. I am trying to create a formula that will add (total sum) of the #'s of sneakers bought per customer (0-17) which is in column A:A - Pink Nike's ONLY?

    Thanks in advance!

  72. Jim says:

    I have a Summary sheet full of all of the products which are available. It contains a Total number of products. The products are Booked In on one sheet and are then Booked Out from another, the Summary sheet contains a SUMIF-SUMIF which calculates the total. I require a warning system or conditional formatting to indicate the Min/Max for a total. Not all products have the same Min/Max.

    The problem that I am having is that if I set up conditional formatting for specific cells and a new product is added, it moves the conditional formatting to another cell. In theory is it possible to make sure the conditional formatting sticks with its specific cell/product number? Or is there another method?

  73. Innocent nIRINGIYIMANA says:

    Thanks for helping me about the use of IF operator.
    But I want to ask you how to use NOT operator within the AND and OR statement.

  74. HisTreeNut says:

    I am working on an monthly evaluation form for employees. It needs to sum cells and calculate a percentage but only if data is entered in a cell. An example of the basic set-up of the sheet is:

    Professionalism: [Graded on a scale of 1 to 10]
    Positive Attitude
    Ability to relate to peers
    Ability to relate to supers
    Ability to relate to customers
    Communicate orally
    Communicate verbally
    Support other beliefs
    Respect differing opinions

    Every employee would be graded each month and the scores would be averaged to give a percentage, but only if data is entered.

    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
    5 6 7 60%

  75. 17Always says:

    Hello,

    Please help me with the formula/function to be used in order that i can get the total sum of the credit limit of a group of customers as shown below:
    Customer Credit Orders
    Cust#1 100 17
    Cust#2 200 100
    Cust#2 200 50
    Cust#3 300 100
    Cust#3 300 150
    Cust#3 300 50
    -------------------------
    Total 600 467
    =========================

    Thanks a lot!

  76. prakash says:

    help to make the formula to count total of following.
    1 2 3 4 5 total
    P P P H P ???

    if p=1,than total sum=??

  77. LINDSAY says:

    This is exactly what I was looking for, thank you!!

  78. JOLLY. says:

    THANKS FOR THE SWIFT RESPONSE.

    THE PROBLEM NOW IS THAT THE RESULT IS SHOWING A MINUS SIGN. MAYBE BECAUSE I ALREADY HAVE FORMULAS IN BOTH CELL A3 AND B2.HOW DO I CREATE THIS FORMULAR WITH GETTING A (-) SIGN. THANKS

  79. JOLLY. says:

    IF A3 > B2 , SUBTRACT B2 FROM A3, AND IF B2 > A3 ,SUBTRACT A3 FROM B2. WITHOUT GETTING A MINUS(-) SIGN. BOTH CELL A3 AND B2 CONTAINS FORMULAS.

    KINDLY ASSIST.

    • Hi Jolly,

      Because you are always subtracting a smaller number from a larger number, there should be no minus sign in the result. It makes no difference whether the referenced cells are filled manually or using other formulas. It's difficult to say what is causing the minus sign to appear without seeing your worksheet. If you can send it to me (support@ablebits.com Att: Svetlana), I will try to help.

  80. JOLLY. says:

    Thanks.

    Please can one do nested If over 64 level of nexting?..

    Kindly advise.

    Thanks,
    Jolly.O.A

    • Hi Jolly,

      To my best knowledge, there is no way to nest more than 64 levels of If in Excel. You can check out the following alternatives to nested IF's. For example, you can use the CONCATENATE function that allows supplying up to 255 arguments in modern Excel versions, which equates to testing 255 different conditions.

      BTW, did you get my email regarding the minus sign? In case it did not reach you for some reason, the problem was in your nested IF formula in cell B5. Because the numbers are enclosed in double quotes, Excel perceives them as text strings, and as the result other formulas referencing B5 yield wrong results. As soon as you remove the quotation marks, both formulas will work fine, and no minus sign will appear in B7.

  81. Ketan says:

    i am using this =SUMIF(A3:A33,"<="&TODAY(),K3:K33) excel formula to calculate MTD whhich will compute sum upto today as i enter data everyday.

    however need help to see how to calculate MTD average percentage !

    [Note: column A being Date 1 to 31 and
    column K being Occupancy%]

  82. Busy says:

    =SUMIF(A2:A10,"",C2:C10) sums values in column C if the corresponding cells in column A are not blank, i.e. they have some content. Therefore it will include values where the cell in A column contains a formula even if its result is "".

    To work around this issue try =SUMIF(A2:A10,"><",C2:C10) instead.

  83. venugopal says:

    i need sumifs total for each person

    A b c d e f
    john 19,018 2,408 1,000 2,000 16,610 145,341
    babu 4,563 578 1,000 2,000 3,985 15
    raju 62,865 7,961 1,000 2,000 54,904 346,323
    john 125,000 15,830 1,000 2,000 109,170 109,170
    babu 63,697 8,066 1,000 2,000 55,631 55,631
    raju 100,000 12,664 1,000 2,000 87,336 87,336
    raju 50,806 6,434 1,000 2,000 44,372 1,352
    raju 100,000 12,664 1,000 2,000 153 87,336
    raju 59,115 7,486 1,000 5,000 132 51,629
    babu 39,098 4,951 1,000 5,000 1,546 34,147
    babu 500,000 63,319 1,000 5,000 15,463 436,681
    babu 25,000 3,166 1,000 5,000 1,566 21,834
    john 181,934 23,040 1,000 13,200 19,761 6,301
    john 154,973 19,625 1,000 16,565 135,348 135,348
    john 25,898 3,280 1,000 45,646 14,564 22,618
    john 150,000 18,996 1,000 54,644 1,564 131,004
    babu 612,971 12,019 1,000 5,465 68,546 600,952
    babu 325,000 1,000 456,468 74,563 325,000

  84. Mohit says:

    Can anyone help me I have this 5000 list 1) column1- pan / name of customer
    2)column various transaction types like P/T/S/R (as per the customer transaction details its short)
    3) consists of total amounts and one customer has many transaction.. I used Sumif formula to get their total but I want a specific customer name showing total amount where as only transaction of (P)..
    Please help

  85. Louise says:

    amt 3k if % less than 50%. to calculate total amt x 106%
    amt 3k if % more than 50%. to calculate total amt x 115%
    can advise on the formula?

  86. sarvaraj says:

    how to use sumif formula for datewise total for particular party and party's total for particular date in one coloumn

  87. JOLLY. says:

    Thanks Svetlana. You have really helped a great deal.

    Please, how can i find two cells having same numbers in excel. For example if A1 and B1 with numbers like A1=34 and B1=39. how can i find cells side by side with same value as 34,39. e.g H15=34,I15=39.

    Thanks,
    Jolly.O.A

  88. Ram says:

    I have table like below: how could I sum the cells that contain "S" in each column? help would be appreciated. in the below table the output should be .4 for column1 and .5 for column2

    .5E .5E
    .2U

    .2G
    .1S .2S
    .1L .1L
    .1U

    .3S .3S
    .7E .7E

  89. Fotis says:

    Hello need help for the following:
    i have 2 collums, in collum A i have part no in collum B i have quantity.
    Part numbers in collum A may be the same in more than one row.

    So what i want to do is that.

    find same part numbers in collum A, then sum their quantity from collum B and give me the total in collum c.
    Something like that:

    A B C
    200855 10
    200855 15
    25

    200856 25
    200856 15
    40

    Thank you

  90. Amanda says:

    I want to sum a range of numbers based on a specific date -30 days. I am trying to sum up the hours flown in the past 30 days, 30 days, 90 days.

    ex: 9/20/2016-30 days
    9/20/2016-60 days

  91. Jayden says:

    Morning,

    Probably simple but cannot get excel to do the following,

    I want a cell to auto count when a single date is entered multiple times in a separate tab,

    Ex - if 1/09/16 is entered 4 times then the cell in the other tab will count 4
    if 2/09/2016 is entered 5 times then the cell in the other tab will count 5 and then if entered more times or deleted will auto adjust itself.

  92. Ebenezer Amegashie says:

    my teacher told me to explain these formulars = sum(A2:F2)

  93. Ebenezer Amegashie says:

    how can I

  94. Kishan says:

    Hi,

    I have a table.

    Name Subjects Marks
    Amy Maths 58
    Kishan Maths 78
    Leon Maths 68
    Ankit Maths 65
    Rey Maths 53
    Amy Science 57
    Kishan Science 65
    Leon Science 92
    Ankit Science 39
    Rey Science 98
    Amy Social Science 69
    Kishan Social Science 98
    Leon Social Science 56
    Ankit Social Science 87
    Rey Social Science 96
    Amy English 78
    Kishan English 68
    Leon English 54
    Ankit English 63
    Rey English 97

    I have to copy same data in other table suing countifs. Can you please help me?

    SL# Maths Science Social Science English
    Amy
    Kishan
    Leon
    Ankit
    Rey
    Average

  95. Like all other Excel Sum functions, SUMIF can only add up numbers, text values will be ignored. If there are some text-formatted digits in the column, you will have to convert them to numbers first, and you can find a handful of easy ways here: How to convert text-formatted digits to number in Excel.

  96. Chris Fox says:

    Hi I am struggling with a cashflow and am hoping someone can help.

    I have a cashflow laid out like this:

    October 2016 November 2016 December 2016 January 2017 February 2017
    £5000 £5000 £5000 £5000 £5000

    I am trying to get a formula which automatically adjusts and counts the total of the cashflows from todays date to the end of my lease. Ie if todays date is November than count cash flows from November until end date.

    Thanks

  97. Jina says:

    How do you add cells in columnB. If B1 to B365 is equal to 1. If there is zero do not add and start again to the first row that is equal to 1. I need to know if there is straight 30, 60 and 90 days. Column A is the date field and Column B is the number of days without late, thus, contains either 1 (without late) or 0 (with late).
    Ho many 30 days in a year. How many 60 days and how many 90 days without late?
    I will appreciate any help.

  98. Dandy says:

    I have 2 worksheets (1 is Contributions given - the other a Category Balance sheet). On the Contributions sheet I have Date, Name, Amount, Tithes, General Fund, Sunday School, etc. I will have multiple entries for each Date & I want to take the totals by Date & put on the Category Balance sheet. How can I create a formula that will look in the date column & total up each category based upon the date & autopopulate the results into the Category Balance sheet for each category?

  99. JOLLY. says:

    Please, how can i find two cells having same numbers in excel. For example if A1 and B1 with numbers like A1=34 and B1=39. how can i find cells side by side with same value as 34,39. e.g H15=34,I15=39.

    Thanks,
    Jolly.O.A

  100. Imad says:

    You are Great!
    I have solved a problem that took a long time.
    Thank you so much

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard