How to use SUMIF function in Excel with formula examples

This tutorial explains the Excel SUMIF function in plain English. The main focus is on real-life formula examples with all kinds of criteria including text, numbers, dates, wildcards, blanks and non-blanks.

Microsoft Excel has a handful of functions to summarize large data sets for reports and analyses. One of the most useful functions that can help you make sense of an incomprehensible set of diverse data is SUMIF. Instead of adding up all numbers in a range, it lets you sum only those values that meet your criteria.

So, whenever your task requires conditional sum in Excel, the SUMIF function is what you need. A good thing is that the function is available in all versions, from Excel 2000 through Excel 365. Another great thing is that once you've learned SUMIF, it will take you very little effort to master other "IF" functions such as SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, etc.

SUMIF in Excel - syntax and basic uses

The SUMIF function, also known as Excel conditional sum, is used to add up cell values based on a certain condition.

The function is available in Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.

The syntax is as follows:

SUMIF(range, criteria, [sum_range])

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

  • Range (required) - the range of cells to be evaluated by criteria.
  • Criteria (required) - the condition that must be met. It 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", etc.
  • Sum_range (optional) - the range to sum if the condition is met. If omitted, then range is summed.
Note. Please pay attention that any text criteria or criteria containing logical operators must be enclosed in double quotation marks, e.g. "apples", ">10". Cell references should be used without the quotation marks, otherwise they would be treated as text strings.

Basic SUMIF formula

To better understand the SUMIF syntax, consider the following example. Suppose you have a list of products in column A, regions in column B, and sales amounts in column C. Your goal is to get a total of sales for a specific region, say North. To have it done, let's build an Excel SUMIF formula in its simplest form.

You start with defining the following arguments:

  • Range - a list of regions (B2:B10).
  • Criteria - "North" or a cell containing the region of interest (F1).
  • Sum_range - the sales amounts to be added up (C2:C10).

Putting the arguments together, we get the following formula:

=SUMIF(B2:B10, "north", C2:C10)

or

=SUMIF(B2:B10, F1, C2:C10)

Both formulas only sum sales in the North region:
Using SUMIF function in Excel

Note. The sum_range parameter actually defines 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 sum_range argument does not necessarily have to be of the same size as range argument, i. e. it may have a different number of rows and columns. However, the top left cell must always be the right one. For example, in the above formula, you can supply C2, or C2:C4, or even C2:C100 as the sum_range argument, and the result will still be correct. However, the best practice is to provide equally sized range and sum_range.

How to use SUMIF in Excel - formula examples

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

SUMIF greater than or less than

To sum numbers greater than or less than a particular value, configure the SUMIF criteria with one of the following logical operators:

  • Greater than (>)
  • Greater than or equal to (>=)
  • Less than (<)
  • Less than or equal to (<=)

In the table below, supposing you wish to add up the sales numbers for the items that ship in 3 or more days. To express this condition, put a comparison operator (>) before the number and surround the construction in double quotes:

=SUMIF(C2:C10, ">3", B2:B10)

If the target number is in another cell, say F1, concatenate the logical operator and cell reference:

=SUMIF(C2:C10, ">"&F1, B2:B10)
SUM IF greater than

In a similar manner, you can sum values smaller than a given number. For this, use the less than (<) operator:

=SUMIF(C2:C10, "<3", B2:B10)

SUM IF equal to

A SUMIF formula with the "equal to" criteria works for both numbers and text. In such criteria, the equals sign is not actually required.

For instance, to find a total of the items that ship in 3 days, either of the below formulas will do:

=SUMIF(C2:C10, 3, B2:B10)

or

=SUMIF(C2:C10, "=3", B2:B10)

To sum if equal to cell, supply a cell reference for criteria:

=SUMIF(C2:C10, F1, B2:B10)

Where B2:B10 are the amounts, C2:C10 is the shipment duration, and F1 is the desired delivery time.
SUM IF equal to cell

Likewise, you can use the "equal to" criteria with text values. For instance, to add up the Apples amounts, choose any of the formulas below:

=SUMIF(A2:A10, "apples", B2:B10)

=SUMIF(A2:A10, "=apples", B2:B10)

=SUMIF(A2:A10, F1, B2:B10)

Where A2:A10 is the list of items to compare against the value in F1.
SUM IF equal to text

The above formulas imply that the criterion matches the entire cell contents. Consequently, the SUMIF function will add up Apples sales but not, say, Green Apples. To sum partial matches, construct the "if cell contains" criteria like in this SUMIF wildcard formula.

Note. Please pay attention that, in Excel SUMIF formulas, a comparison or equals operator should always be enclosed in double quotes, whether used on its own or together with a number or text.

SUM IF not equal to

To build the "not equal to" criteria, use the "<>" logical operator.

When a value, either text or number, is hardcoded in the criteria, remember to surround the entire construction with double quotes.

For example, to sum the amounts with shipment other than 3 days, the formula goes as follows:

=SUMIF(C2:C10, "<>3", B2:B10)

To find a total of all the items except Apples, the formula is:

=SUMIF(A2:A10, "<>apples", B2:B10)

When the criterion is in another cell, concatenate the "not equal to" operator and a cell reference like this:

=SUMIF(A2:A10, "<>"&F1, B2:B10)
SUM IF not equal to

SUM IF blank

This example shows how to sum cells in one column if a corresponding cell in another column is blank. There are two formulas to fulfill the task. Which one to use depends on your interpretation of a "blank cell".

If "blank" means cells that contain absolutely nothing (no formula, no zero-length string returned by some other function), then use "=" for criteria. For example:

=SUMIF(B2:B10, "=", C2:D10)

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

=SUMIF(B2:B10, "", C2:D10)

Both formulas return a total of sales for undefined regions, i.e. where a cell in column B is blank:
SUM IF blank

SUM IF not blank

To make "if cell is not blank then sum" kind of formula, use "<>" as the criteria. This will add up all cells that contain anything in them, including zero-length strings.

For instance, here's how you can sum sales for all the regions, i.e. where column B is not blank:

=SUMIF(B2:B10, "<>", C2:D10)
SUM IF not blank

Excel SUMIF with text criteria

When adding up numbers in one column based on text values in another column, it's important to differentiate between exact and partial match.

Criteria Formula Example Description
Sum if equal to Exact match:
=SUMIF(A2:A8, "bananas", C2:C8)
Sum values in cells C2:C8 if a cell in column A in the same row contains exactly the word "bananas" and no other words or characters. Cells containing "green bananas", "bananas green", or "bananas!" are not included.
Sum if cell contains 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", "bananas green", or "bananas!" are summed.
Sum if not equal to Exact match:
=SUMIF(A2:A8, "<>bananas", C2:C8)
Sum values in cells C2:C8 if a 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.
Sum if cell does not contain Partial match:
=SUMIF(A2:A8, "<>*bananas*", C2:C8)
Sum values in cells C2:C8 if a 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 real-life formula examples, please check out Sum if equal to and Sum if not equal to.

In the next section, we'll take a closer look at SUMIF formulas with partial match.

SUMIF formulas with wildcard characters

To conditionally sum cells by partial match, include one of the following wildcard characters in your criteria:

  • Question mark (?) to match any single character in a specific position.
  • Asterisk (*) to match any number of characters.

Example 1. Sum values based on partial match

Suppose you wish to total sales for all northern regions, including North, North-East, and North-West. To have it done, put an asterisk right after the word "north":

=SUMIF(B2:B10, "north*", C2:D10)

An asterisk on both sides will also work:

=SUMIF(B2:B10, "*north*", C2:D10)

Alternatively, you can type the region of interest in a predefined cell (F1), and then concatenate a cell reference and a wildcard character enclosed in quotes:

=SUMIF(B2:B10, F1&"*", C2:D10)

Or

=SUMIF(B2:B10, "*"&F1&"*", C2:D10)
SUMIF wildcard formula

Example 2. Sum if cell contains * or ?

To match a literal question mark or asterisk, place a tilde (~) before the character, e.g. "~?" or "~*".

For example, to sum sales for the regions marked with *, use "*~*" for criteria. In this case, the first asterisk is a wildcard and the second one is a literal asterisk character:

=SUMIF(B2:B10, "*~*", C2:D10)

If the criteria (* in our case) is entered in a separate cell, then concatenate a tilde and the cell reference, like this:

=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)
SUMIF formula with an asterisk as criteria

Example 3. Sum if another cell contains text

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

To add up values in cells C2:C8 if a cell in column A contains any text character(s):

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

To sum values in C2:C8 if a cell in column A contains any text value, including zero length strings:

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

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

How to use Excel SUMIF with dates

Using dates as SUMIF criteria is very much like using numbers. The most important thing is to supply a date in the format that Excel understands. If you are not sure which date format is supported and which is not, the DATE function can be a solution.

Assuming you are looking to sum sales for the items delivered before 10-Sep-2020, the criteria can be expressed in this way:

=SUMIF(C2:C10, "<9/10/2020", B2:B10)

or

=SUMIF(C2:C10, "<"&DATE(2020,9,10), B2:B10)

or

=SUMIF(C2:C10, "<"&F1, B2:B10)

Where F1 is the target date.
SUMIF using dates as criteria

To sum cells based on today's date, include the TODAY function in your criteria. For example, that's how you calculate a total of sales with a delivery date prior to today:

=SUMIF(C2:C10, "<"&TODAY(), B2:B10)
Sum cells based on today's date

To sum within a date range, you need to define a smaller and larger date separately. This can be done with the help of the SUMIFS function that supports multiple criteria.

For example, to sum values in column B if a date in column C is between two dates, this is the formula to use:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)

Where B2:B10 is the sum range, C2:C10 is the list of dates to check, F1 is the start date and G1 is the end date.
SUMIF between two dates

More formula examples can be found in SUMIFS with date range as criteria.

How to do SUMIF from another sheet

To conditionally sum data from a different sheet, provide external references for the SUMIF arguments. The easiest way is to start typing a formula, switch to another worksheet and select ranges using the mouse. Excel will insert all the references automatically, without you having to worry about the correct syntax.

For instance, the below formula will add up values in C2:C10 on the Data sheet based on the criteria in B3 on Sheet1:

=SUMIF(Data!B2:B10, B3, Data!C2:C10)
SUMIF from another sheet

How to correctly use cell references in SUMIF criteria

To create a flexible formula, you normally insert all variable parameters in predefined cells instead of "hardcoding" them. With Excel SUMIF, that might be a bit of a challenge.

In the simplest case when summing "if equal to", you simply use a cell reference for criteria. For example:

=SUMIF(C2:C10, F1, B2:B10)

But when a cell reference is used together with a logical operator, the criteria should be provided in the form of a string. So, you use the double quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off. For example:

=SUMIF(C2:C10, ">"&F7, B2:B10)

Please note that the comparison operators are enclosed in quotation marks while the cell references are not.
Correct use of cell references in SUMIF criteria

Why is my SUMIF formula 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. Below is a list of important things to check.

1. SUMIF supports only one condition

The syntax of the SUMIF function has room for only one condition. To sum with multiple criteria, either use the SUMIFS function (adds up cells that meet all the conditions) or build a SUMIF formula with multiple OR criteria (sums cells that meet any of the conditions).

2. Range and sum_range should be of the same size

For a SUMIF formula to work correctly, the range and sum_range argument should have the same dimensions, otherwise you may get misleading results. The point is that Microsoft Excel does not rely on the user's ability to provide matching ranges, and to avoid possible inconsistency issues, it determines the sum range automatically in this way:

Sum_range defines only the upper left cell of the range that will be summed, the remaining area is determined by the size and shape of the range argument.

Given the above, the below formula will actually sum cells in C2:C10 and not in C2:D10. Why? Because range consists of 1 column and 9 rows, and so does sum_range.

=SUMIF(B2:B10, "north", C2:D10)

In older Excel versions, unequally sized ranges can cause lots of problems. In modern Excel, complex SUMIF formulas where sum_range has less rows and/or columns than range are also capricious. That is why it's a good practice to always define the same number of rows and columns for these two arguments.

3. Range and sum_range should be ranges, not arrays

Though SUMIF can process an array constant in criteria like shown in this example, it does not support arrays in range and sum_range. These two arguments can only be cell ranges.

5. SUMIF criteria syntax

For criteria, the SUMIF function allows using different data types including text, numbers, dates, cell references, logical operators (>, <, =, <>), wildcard characters (?, *, ~) and other functions. The syntax of such criteria is quite specific.

If the criteria argument includes a text value, wildcard character or logical operator followed by text, number or date, enclose the whole criteria in quotation marks. For example:

=SUMIF(B2:B10, "north*", C2:D10)

=SUMIF(C2:D10, ">100")

=SUMIF(B2:B10, "<>north", C2:D10)

=SUMIF(C2:C10, "<=9/10/2020", B2:B10)

When a logical operator is followed by a cell reference or another function, the criteria should be provided in the form of a string. So, you use an ampersand (&) to concatenate a logical operator and a reference or function. For example:

=SUMIF(B2:B10, ">"&F1)

=SUMIF(C2:D10, "<="&TODAY(), B2:B10)

4. SUMIF from another workbook not working

As with many Excel functions, SUMIF can refer to other sheets and workbooks, provided they are currently open.

For example, this formula will work fine as long as Book1 is open:

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

And it will stop working as soon as Book1 is closed. This happens because the referenced ranges in closed workbooks get de-referenced into arrays. And since arrays are not supported in the range and sum_range arguments, SUMIF throws a #VALUE! error.

6. SUMIF does not recognize text case

By design, SUMIF in Excel is not case-sensitive, meaning it treats uppercase and lowercase letters as the same characters. To make a case-sensitive SUMIF formula, use the SUMPRODUCT function together with EXACT.

That's how to use SUMIF in Excel. Hopefully, our formula examples have given you some good insights. As always, I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel SUMIF examples (.xlsx file)

You may also be interested in:

284 comments to "How to use SUMIF function in Excel with formula examples"

  1. Kseniia Philippidis says:

    Hi , I'm trying to make a formula for today profit , so it updates every day based on today date I'm putting it as =sumif(range,"today()",sum_range And for sum reason it always shows $0 , do you know what the formula should be in this case?

  2. Danielle says:

    Hi I am in the process of completing MOS 201 Objective 3.1. Not sure if you are familiar with the book but I cannot seem to figure out this sub objective. I tried different ways and I had gotten the answers but I am still getting (DiV/0) which should not be.

    On the Gross Margin worksheet, add formulas to the Gross Margin field (H7:H14) that calculate the gross margin by subtracting Cost from Retail and then dividing by Cost. To avoid #DIV/0! (division by 0) errors, wrap the calculation inside an IF function that returns the gross margin if Cost is not 0, or the message Cost is 0! otherwise.

  3. mike says:

    trying to create a Gannt chart. part of this is a list of tasks and sub tasks which are assigned numbers e.g 3 and this might have a sub task 3.1 which again may have a sub task 3.1.1
    to each task or sub task is assigned a duration in days. the duration will be assigned to the lowest sub task in this case task 3.1.1 So i want a formula that will sum up the duration for the subtasks that fall under its "jurisdiction" so for subtask 3.1 it would include durations assigned to task 3.1.1, 3.1.2.... 3.1.n but not any time associated with any other subtasks under task heading like 3.2 or 3.2.1 or 4 or 4.1.
    i would like the formula to be able to be dragged to autofill cells beneath and the formula still work

  4. MJ McCarthy says:

    I need to sum a vast list but only three columns. A is the technician name, B is the cost of labor, C is the invoice. One invoice can have a dozen entries and I just need the total for that invoice. I'm a novice, thanks.

    JOE SMITH 25.00 338117
    JOE SMITH 5.00 338117
    JOE SMITH 12.50 338118
    JOE SMITH 262.50 338150
    JOE SMITH 57.50 338160
    JOE SMITH 12.50 338160
    JOE SMITH 7.50 338160
    JOE SMITH 5.00 338160
    JOE SMITH -2.50 338160
    JOHN JONES 22.50 338161
    JOHN JONES 12.50 338161
    JOHN JONES 10.00 338161
    JOHN JONES 5.00 338161

  5. Don says:

    I want to sum the values in column B which satisfies that any data in column A is greater than 7 but less than 13.
    How do i write the formular?
    Kindly help.
    Thanks.

  6. Laurie says:

    My issues is that I need to calculate several cells together but only if a name exists in the first cell of the row,
    B C D E F G H
    12 kl 50 28 13 18.1 50.5. TOTAL

    so B12 would be a name and if no name appears in that cell then there is no total

    • Laurie says:

      this is what I am working with

      B19 c19 D19 E19 F19 G19 H19 I19 J19 K19 L19 M19 O19 P19 Q19
      JOSH 50 28 13 18.1 50.5 10 20.50 9.16 =sum of c19:p19

      meg 50 28 13 18.1 50.5 10 20.50 9.16 =sum of c20:p20

      I need the sum of c:q for the total of monthly fees. I only want it to total if there is a name in the B column. The name will change in each row.

      I tried the first formula and I am getting a blank space

  7. Carlos says:

    =SUMIFS(G22:G114,$K$22:$K$114,2022)

    What if I want to take the value in G:22:G114 and divide it by a number in another column, say N:22:N114, before adding.

    The value in the N could be different for each row. So in this example if the value for G26 is 1000 and the value for N26 is 4 then it would only add 250

    The next row could have a value of 400 in the G column and a 2 in the N column and it would add 200.

    So at this point I would have a value of 450.

  8. Adarsh says:

    How to use SumIF formula to calculate commission
    Problem: Minimum Guarantee is 1000 or 10% of sales whichever is higher. Please explain how to use the formula. Thanks

  9. Viktor Gregor says:

    I want just to thank you for explaining the use of asterisk in condition of SUMIF to find certain word in a string (e.g. "*apple*"). A discovery for me :-)

  10. Sukhchand Mehar says:

    for Example
    item id item name Qnty Stock
    47158 Whirlpool Double Door Refrigerator 340L IF INV CNV 355 3S Arctic Steel 1 Yes
    47435 Avast Gift Voucher Premium Security MRP 1499 1 No
    i want to only yes quantity in other sheet against item id please help

  11. CATHERINE ONG says:

    if i got cell A1 to AA1,but i only require to sum every 1,3,5,7,9....column
    How should i set the sumif formula?

    Thanks in advance

  12. Rona Croes says:

    Is it possible ta change the cell color of two cells when these two cells are greater than 70% when summed together?

  13. Ed says:

    SUMIF question. I have this formula which works fine. =SUMIF(E4:E319,">.2",C4:C319). However, I would like the ">.2" part of the formula to be in a cell where you can change the value from .2 to whatever you would like. Is that possible?

    Thanks in advance!

  14. Rajkumar says:

    I want to use sumif function where input range to evaluate is output of Vlookup function, but it does not work as the output of sumif is always coming "0"

    • Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  15. Huzefa says:

    Hi,
    I have data of purchases of several different type of items on different dates and different quantities throughout the year. I want to apply such formula that I get a report in which it gives total purchase quantity for particular period, say Aug 15 to Oct 10, for the item written in A1 cells.

  16. r.bala murugan says:

    Sir, Thank you very much for your support. My query is below.
    10001 10003 10005 10006 10007 Total
    7712 398 250 125 4700 13185
    5784 299 250 325 4667 11325
    1) I want to sum if the Header (Criteria) 10001,10005
    2) I want to sum if the header "define name" - Criteria used defined name

  17. R.BALA MURUGAN says:

    I write sumifs function as below with multiple criteria. But showing error.
    Kindly advise to solve.

    =SUM(IF(K1:BE1{"10001","10003"},K134:BE134,0))
    =SUMIFS(K134:BE134,K1:BE1,"10001,10003")
    =SUMIFS(K134:BE134,K1:BE1, hcode)
    hcode as range
    hcode contains 10001,10003

    • Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please specify what you were trying to find, what formula you used and what problem or error occurred.The first formula violates the syntax rules of the IF function. The data ranges in the SUMIFS function must be the same size. You also violated the syntax rules. Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

  18. Bec says:

    Hi, I'd really appreciate your help with something.
    I'm trying to work out the number of days between 2 dates, but if there is no date in column B, I would like column C to read "Not Seen". Here's an example of a date in column B and then no date in column B.
    Column A Column B Column C
    1/6/2020 6/6/2020 5 (using formula =B2-A2)
    1/6/2020 -45778 (this is what's happening now)
    1/6/2020 Not Seen (this is what I would like to have)

    Thanks in advance

  19. Sandamal Priyankara says:

    =SUMIF($A$17:$A$28,A2&">0",$D$17:$D$28) is it Correct ">0"

    Plus Valuve Minus Valuve
    01/07/2020 - -
    02/07/2020 - -
    03/07/2020 - -
    04/07/2020 - -
    05/07/2020 - -
    06/07/2020 - -
    07/07/2020 - -
    08/07/2020 - -

    01/07/2020 $1,000.00
    01/07/2020 $2,000.00
    01/07/2020 $5,000.00
    02/07/2020 $(500.00)
    02/07/2020 $(10,000.00)

  20. Nancy says:

    so I have a simple thing that for some reason I cannot make happen. I need to sum a range of cells, but I need to only show the value of the cell that is 12 or less in the cell, but still allow the next cell to show what is over 12.
    this is a payroll issue to separated out overtime...
    so.. we have 4 areas that employees can work hours, I need to total those 4 cells and have only the first 12 hrs actually show in the reg hours worked and then anything over 12 will show in the overtime.
    Seems simple -- but so not for me!

  21. Ajaya says:

    Here Khata Column followed by "do" I want to add Area Field

    Khata Plot Area Rent Cess
    1 361 0.18 5.00 3.00
    do 367 0.155 5.00 2.50
    do 360 0.165 5.00 2.50
    2 413 0.068 0.80 0.50
    3 348 1.128 13.80 10.50
    5 206 0.63 6.30 3.00
    6 253 0.065 6.45 3.00
    do 254 0.085 6.45 3.00
    do 257 0.08 6.45 3.00
    do 255 0.025 6.45 3.00
    do 256 0.008 6.45 3.00
    do 250 0.138 6.45 3.00
    do 251 0.066 6.45 3.00
    do 252 0.03 6.45 3.00

  22. Rotary says:

    I need to calculate the following table
    f 2
    m 1
    m 5
    f 2
    f 4
    f 1
    m 0
    m 1
    f 1
    in this two columns, column 1 = m & f, column 2 = figures, i need how many 0-5 f's in figures column and 0-5 m's. no need the total need only the number of, please help

  23. Ann says:

    Hi, I am currently using the "SUMIF in combination with the TODAY()". I have a problem with the weekends. If I use the "<"&Today(), unfortunately on the 06.01.2020 no value is provided. Is there a way how i can exclude the weekends in the formula and still obtain the values? I wish to avoid changing the formula manually. I rather have a formula which I can drag and obtain the correct values. Thanking you in advance for your help.
    03.01.2020
    06.01.2020
    07.01.2020
    08.01.2020
    09.01.2020
    10.01.2020

  24. anil babu says:

    =IF(AND(TEXT('Outbound data'!K:K,"0")="TFS-01.MATE,KHM.ALI",TEXT('Outbound data'!L:L,"0")="110018144"),SUMIF('Outbound data'!L:R,$E5,'Outbound data'!R:R),"0")
    I want to compare two places if two places value match then respective line next row value sum should appear.

  25. Nagaraju says:

    Dear Sir/Madam,
    I want to filter some text but I can,t. Please help in this matter. The need is.... I have pending customers list. And the Heading is "Serial NO., Name, Mobile No., Due month& Year".

    I want filter the Year before 2017 and don't Show the customer Details entire the serial number linked with the Year I filter. Please help me.

  26. Quentin says:

    hi i dont know if this is the right place to ask but. i have some columns that has either 1 or 0 in them and i need to add up the 1 up to the first 0 and no further.problem is there may be hundreds of 0 in the same column. and the same for 1. so my question is how can i add up all the 1 up to the first 0 and nothing else. ive been doing it manually but it is annoying plus the chance for error is bigger.

  27. HAREESH says:

    Sir I need a help.
    I want to addition 2 column with a condition ie
    need calculation in U4,condition is that if column no "N4" is >= column No F4, need 0 if not, need original amount while adding N4+T4

  28. HAREESH says:

    Sir I need a help.
    I want to addition 2 column with a condition ie
    need calculation in U4,condition is that if column no "N4" is >= column No F4, need 0 if not, need original amount while adding N4+T4

    • andy says:

      hi,i am struggling with calculating data.
      how to calculate if range of specified text is met then sum range greater than 0 in one cell.
      eg. IF(A2:A50,"abc") THEN SUMIF(F2:J15,"<0")

      Thank you for looking in my post.

  29. Mazen says:

    Hello there,

    I need a formula to do the following...
    if column D contains today's date, and column F contains "apple" then SUM column E.

    Could you please help me with that... ?

  30. Sohaib says:

    Thankyou so much for being help us. but here i want to ask how we can copy text statement from one sheet to another sheet so that if we changed in master sheet the change could appear in other sheet automatically.

  31. Cindy says:

    Thank you so much for this site. I was able to finally figure out the function that I needed and how to write the different parts.

  32. Ahmed says:

    Hi, Please advise me an equation for following situation.
    I have a tracking table for tracking Design request enquires.
    i need to track on MONTHLY basis total number of Design Requests with Status (open / Close) and Overdue (Yes / No)
    Appreciate your support.

  33. Amy Yee says:

    Hi, how to combine both formulas in one cell?
    =SUM(H31,I31,J31)
    =IF(H31<I31,I31)+If(H31<J31,J31)
    I need to sum 3 coloums (H,I,J) but if I & J have values then H will need to subtract.
    Thanks & regards,
    Amy

  34. Gerry says:

    I wish to be able to allow a cell ( D6 )$ amount to be included in the column ( D1 to D20 )total if the next cell ( E6 )is marked with a check symbol ( wingdings 2 ). It said cell ( E6 ) is marked with an x ( wingdings 2 ), then the cell ( D6 ) with the $ is not included in total. And so on down the page for my monthly bills accounting.

  35. DB says:

    I'm trying to develop a mileage tracking sheet for both personal and business entries. Column A is the odometer when I arrive at a certain location. Column B will have a "Y" if the mileage is personal in that row. Column C is the total miles driven since the last stop if it is for business, no "Y" in column B for that row. Column D is the total miles driven since the last stop if it is for personal, "Y" in the B column for that row. Cell H2 contains a running total of business miles for the curent month. Cell H4 contains a running total of personal miles for the current month. I can't figure out how to subtract, for example, cell A5 from cell A6 using the SUMIF or SUMIFS function. I think I can write a formula for a running total for personal and business miles driven, but if you can suggest a formula I would appreciate it. Do I need to write a formula or include in the formula where to place the total miles driven for any certain row in either column C or D? Meaning for business miles driven on a single drive, no "Y" in column B for that row, and place that number in column C for that row and leave column D for that row blank. As you can tell, I'm struggling with these formulas. Thank you in advance for your help. DB

  36. G says:

    Substantially important point below, don't think I've seen this nuance pointed out anywhere else!

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

  37. Peter says:

    So I'm running into some issues with a compound SUMIF formula.

    =SUMIF(AND(A8:A200,G8:K200,">"&Q1,G8:K200,""&0)

    The main issue is that there are missing values for the lines that have a 0 in the "count" column as the information is being pulled from another table where "Count" is compiled using a COUNTIF formula. This being the case, these rows have the #N/A value plugged in if the count column shows a 0 which prevents the data from calculating. Because of this, I am hoping someone may be able to help me out and suggest a way for me to add up the dollar values for any dates that appear in a column that is between Days 1 and 2.

    Essentially I need it to do the following:

    1. Check columns G for any dates within a 1week timeframe
    2. add up the values that have a quantity of 1 or more.

    does anyone have any suggestions?

  38. Phil says:

    Thanks in advance.

    I want to use several Sumifs and all work except for the following.

    In column B there will be a list of numbers and I want the calculation to exclude all numbers that end with the number 2, is this possible? or the reverse add up all the numbers that don't end with the number 2.

    Phil

  39. Bill Hawkes says:

    How can i use Sumif statement with the condition or criteria is a highlighted cell? looking to pull cost based on Green, Yellow, or Red Cells. any help would be great.

  40. maharshi says:

    hi,

    i am maintaining invertory in excel, by the current date
    opening stock has to come with the total of previous for specific item.
    please guide the formula. my formate is as follows

    item Description 19-Jun-18 20-Jun-18 21-Jun-18 22-Jun-18
    Item No.1
    Item No.2

  41. Shabbir says:

    I Want Help On following
    Tax Slab Rate of Tax
    - 400,000 0
    400,001 800,000 1,000
    800,001 1,200,000 2,000
    1,200,001 2,400,000 5% of exceeding of 1,200,000
    2,400,001 4,800,000 10% of exceeding of 2,400,000 & 60,000
    4,800,001 onwards 15% of exceeding of 4,800,000 & 300,000

    Taxable Income Is 3,880,000 Per year
    And Tax Liability 208,000 Per Yeat
    and i used the Following Formula for calculation
    =IF(C11<=B4,0,IF(C11<=A5,1000,IF(C11<=A6,2000,IF(C11<=A7,(C11-B6)*0.05,IF(C11<=A8,(C11-B7)*0.1+60000,IF(C11<=A9,(C11-B8)*0.15+300000))))))

  42. Mohammad Yamin says:

    I want to apply a simple IF formula

    A1-A2=A3
    10-12=0
    10-8=0
    IF the result is <0 A3 should show 0 else the actual value.

    For example A1 value is 10 and A2 value is 12 and I want result in A3 only 0 if the result is <0.

  43. Chris says:

    Hello!

    I am looking to do a Sumifs based on the following conditions:
    Sum = "Good" Column
    Part = "A"
    Step = Smallest value for Part "A" assuming this can change depending on data pull.

    PART STEP GOOD
    A 10 10
    A 10 10
    B 30 5
    B 30 10
    B 50 15
    A 20 10
    B 60 8
    C 20 6
    C 40 10
    C 50 20
    A 30 30
    B 30 40
    C 60 20
    A 30 15

    Thank you, any suggestions would be much appreciated!

    • Doug says:

      Chris:
      Where the data is structured as your sample and is in F36:F49,D36:D49 and E36:E49 enter the "Part" in I36 and the "Step" in I37, then in I38 enter the formula:
      =SUMIFS(F36:F49,D36:D49,I36,E36:E49,I37)
      You can then enter the step and part you're interested in seeing in their respective cells and the Good will be displayed in I38.
      You'll probably want to enter their labels in H36, H37 and H38.

  44. Musaddique Anwar says:

    Hi! Sir
    I am from Malegaon. I am businessman.
    I watched all videos on your youtube channel about excel. All videos are very useful but I have one problem.

    The problem is.........
    CONSIDER:

    this is [ ] first column in excel.
    this is ( ) second column in excel.

    In this [ ] type number.
    In this ( ) number should come automatically.

    In this [ ] column 0 to 80 is capacity and after 80 starts from 1.

    [45] (45) this is 0+45=45
    [80] (35) this is 80-45=35
    [25] (25) this is 0+25=25
    [50] (25) this is 50-25=25
    [10] (40) this is 80-50+10=40
    [60] (50) this is 60-10=50
    [10] (30) this is 80-60+10=30
    [45] (35) this is 45-10=35
    [75] (30) this is 75-45=30
    [15] (20) this is 80-75+15=20
    [50] (35) this is 50-15=35
    [20] (50) this is 80-50+20=50

  45. Sajil says:

    If Cell A says "POS" i need to get 3% of cell B & C at Cell D,
    If Cell A Says "NAG" i need to get 5% of Cell B & C at Cell D

    Could you please help me to find the formula?

  46. ajay says:

    Invoice date at row number 1 is less than October 2014.Invoice Date should be less than Current Date and greater than October 2014.Error in Excel Sheet Data.

  47. Alex says:

    Hi!
    I am working on 2 sheets, sheet 1 & 2 which I re-named GENERAL and SUMMARY.
    The GENERAL sheet is where I do my in & out of materials and the SUMMARY sheet is where I get the balance quantity of the materials.
    e.g. material code on column H and quantity on column K of the GENERAL sheet, then material code on column B and balance quantity on column E of the SUMMARY sheet. I tried =SUMIF(GENERAL!H4:$K4:B4, 3331, GENERAL!B4:$E4, 3331) but failed. Kindly help

    • 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 support@ablebits.com. 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.

  48. Ahmad says:

    Please, guide me
    i want to check master SKU primary volume sale distributor type wise by latest date. format mention below.

    Date Master SKU Distributor Type Primary Volume
    12/5/2017 Olper 200ml Hyperstar - ICA 365
    12/5/2017 Olper 200ml Makro/Metro –ICA 480
    12/6/2017 Olper 200ml Hyperstar - ICA 72
    12/7/2017 Olper 200ml Makro/Metro –ICA 38
    12/5/2017 OMUNG 200 ML Hyperstar - ICA 178
    12/5/2017 OMUNG 200 ML Makro/Metro –ICA 120
    12/6/2017 OMUNG 200 ML Hyperstar - ICA 58
    12/7/2017 OMUNG 200 ML Makro/Metro –ICA 48

  49. Sharon says:

    I have a Y / No column. I want to know how many Yes and how many No. I'm sure this is very simple, that I just can't break out of the simplicity of it.

  50. Evangeline Hurter says:

    Working on a school fundraiser. We offer items for $3 each or 5 for $10. Is there a formula for determining not only the total sold but also calculating the amount of monies that should have been collected?
    Thanks

    • 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 support@ablebits.com. 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.

  51. aas1354 says:

    Hello everyone,
    I have a formula which is like this

    =COUNTIFS('sheet1 '!W:W,">0",'sheet1 '!A:A,"Truck")

    which is count all data in column W (sheet1) larger than 0, which are called Truck in column A.

    Now, I want this formula to read the same information but by consider a range of dates in between 1st of October to 15th (I have dates in Column B).
    Any input is greatly appreciate.
    Thanks

  52. Shameer says:

    I have invoice no in one column its may repeating depend upon how many items in a invoice, i need total value of item that is present in same invoice no

  53. Ahamed Wazeer says:

    Hello

    How can able to calculate Commission as per ton
    conditions are following

    less than equal 60 ton will pay 100$ if more than 60ton additional tons will pay in 150$

    how to formulate for this case which function i need to use
    please help me someone

  54. Darin says:

    Thank you in advance for any assistance.
    I'm trying to SUMIF the amounts in H if they occurred on a given day.

    Formula is =SUMIF(TEXT($A2:$A36, "mdyyyy"), TEXT(G41, "mdyyyy"), $H2:$H36)
    A2= 9/9/2017 (cell formatted as date)
    H2= $2,201.88 (cell formatted as currency)
    G41= 9/9/2017 (cell formatted as date)

    When I view the calculation via fx, everything looks fine, but the result is 0.

    Thank you again for any insight.

    Best,
    Darin

  55. Suraj Kc says:

    I need SUMIF FORMULA for certain 'Entry Name' along with seperation of Date from 1 to 30.
    Example
    A NAME HOURS
    1.9.2017 ABC 2
    1.9.2017 ABC 3
    1.9.2017 XYZ 2
    2.9.2017 XYZ 1
    2.9.2017 ABC 2
    3.9.2017

    How can I get total hours of 1.9.2017 for ABC and XYZ seperately?

    DATE ABC XYZ
    1.9.2017 5 2
    2.9.2017 2 3

    I need total hours for ABC and XYZ on basis of dates and name entered.

    Email: surajkc938@gmail.com

    Thank You!!

  56. Sue says:

    My spreadsheet has one cell (A1) that contains the "pot" of money. One column (cell A2) is for amounts drawn against it with another column (C2) having a balance. So the formula in C2 is =SUM(A1-A2).

    Another column is what was “actually spent”, so if I put a value in that one, I would like to have C2 deduct that instead of the original amount drawn. This column will only have a value if what was actually spent is more or less than original amount.

    I'm looking for a formula something like "=SUM(A1-A2) unless B2 has a value then use that instead of A2”?

  57. Jim says:

    Cell C5 displays the sum of C1:C4
    If I want Cell C6 to show the total in excess of B5
    but if C5 is less than B5 I want it to show 0

    How can I do this?

  58. Asil Zada says:

    MLot# CN Customer Colour Fabric Gry Wt Rate Str Print
    CL-10531 Bal ES Apparel Navy HTR S/J 351.25 95
    CL-15558 Dyd Mass Apparels & Fabrics (PVT) Ltd. H/Grey 2Flc 981.00 50
    CL-16455 Bal Crown Textile Boardex 3Flc 622.00
    CL-16664 Dyd Salman Industries Wind Chime 2Flc 707.05 Yes
    CL-16732 Dyd Salman Industries Peach Whip 2Flc 711.70 Yes Yes
    CL-16900 Dyd Salman Industries H/Grey 2Flc 526.70 90 Yes
    CL-16901 Fnsh Salman Industries Plane Moul 2Flc 974.40 Yes Yes
    CL-16913 Dyd Pelikan Knitwear Blue Flot F/T 579.90 175 Yes
    CL-16990 Fnsh Salman Industries H/Grey 2Flc 978.95 90 Yes Yes
    CL-17058 Dyd M. R. Export H/Grey 3Flc 972.50 Yes
    CL-17192 Dyd Pelikan Knitwear Blue Flot F/T 742.00 175 Yes
    CL-17193 Dyd Zubisma Apparel Navy 3Flc 473.20 Yes Yes
    CL-17244 Dyd Pelikan Knitwear Black 3Flc 440.25 160 Yes
    CL-17250 Dyd M. R. Export Mid Night Navy 2Flc 794.35 Yes
    CL-17278 Dyd Pelikan Knitwear White 2Flc 1002.05 Yes Yes
    CL-17321 Dyd Pelikan Knitwear Charcoal 2Flc 315.00 140 Yes
    CL-17339 Dyd M. R. Export H/Bleach 3Flc 954.95 Yes

    Table[CN]="Dyd" And Table[Str]="Yes" And Table[Print]="Yes"
    Then Sum of Table[Gry Wt] * 20

    Result should be =
    711.7 * 20 + 473.2 * 20 + 1002.05 * 20 = 43,739

  59. Brajesh says:

    Hai

    I have three columns e.g.

    USA 10.08.2017 10.54
    India 10.08.2017 9.45
    USA 11.08.2017 6.54
    USA 12.08.2017 2.98
    India 12.08.2017 1.65

    Now if destination repeats then sum the current value plus value of same destination before 48 hours from current date

    Can you help.

    Thanks so much!

  60. Waqas Ali says:

    Hello

    I am using my sumifs with 2 criteria
    Sumif(a2:a7, d2:d7, >8years, e2:e7, "m")

    The age (d2:d7)I am converting using datedif

    The formula works up to 9years 11 moths, as soon as the age is over 10 years, it does not add numbers in a2:a7

    Any help would be appreciated.
    Thanks

  61. Adi Arpadzic says:

    Hi, i have problem with SUMIF function. My criteria is acrticle code (ex. 031285), and SUMIF bring me result from 031285 and 31285 so it is incorrect then. How to force SUMIF to bring only exactly code article? Thanks!

    • Adi Arpadzic says:

      i need force SUMIF to use my zeros in article code to criteria when finding exact value from that article code.

  62. Alex says:

    I have multiple sheets with the same data and I am able to sum the corresponding data with =SUM(Leuzinger1:Sheet2!C3). Now I want to only sum the value in C3 from a sheet if the value in A1 of that sheet is 1. Can I use sumif? Basically, I need to select all the sheets where A1 == 1 and then sum the values of c3. Thanks for any help!

  63. Gareth says:

    Hi,
    I am trying to write a formula to add up the values in a previous year based on the equivalent amount of recorded days this year to show a Year on Year variance.

    I have a support worksheet that adds up the amount of days / cells that has had data inserted in this year to produce a number. (for example 18 days)

    I have a list of values separated by days from last year and i want to return a value based on 18 days worth of data from that year.

    Could you help please?

  64. Doug says:

    Hello. I'm trying to write a sumifs formula for multiple conditions in which the referenced criteria cells contain formulas and display the results. For example, instead of summing all the cells in a column where the value >=2, I want to sum all the cells where the value is >= a referenced cell that has a formula in it that equals 2. In older versions of Excel I did this with the Conditional Sum Wizard, but I can't seem to get it to work with Sumifs. Can you help.

    Thanks so much!

  65. Johncy says:

    Hi,

    How to sum cells which contains numbers and text either the numbers are in the beginning or at the end? I don't wanna remove the text/number or don't want to separate the text and the number.

    Please give me some idea.

  66. Dean says:

    Hi, hope you can help me to find the best formula for my need.
    I would like to make revenue calculation of several customers.
    there is a commission that is changing between some of them - for example
    1. should be amount - 20%
    2. should be amount + 5%
    3. no extra commission

    I have used =sum with sumif for each one that needs extra commission change but than I don't know how to add in all the rest that don't need any modification without the sumif...my formula:
    =sum(SUMIF(B96,"customer1",E96)*$L$3)+(sumif(B96,"customer2",E96)*$L$4)+(sumif(B96,"customer3",E96))+(sumif(B96,"customer4",E96))-G96

    *note that I added customer 3 and 4 without any modification just so they are counted...but I need something generic for any other partner than 1 and 2
    any idea?

    Thanks!
    Dean

  67. Brett Cilton says:

    Hi,

    I am working on a cash flow. I'm looking to create a formula which basically read's: If (A1) = (B1) then add (C1) to (D1).

    I hope this makes sense.

    Appreciate your help.

  68. Bethany Harris says:

    Hello,
    I am working on an excel sheet for work and I need to create a cell that will add up how many rows I have that are a certain color and display that number of rows in the cell. I also have one that needs to add up the number of rows based on color AND a specific word in a cell within that row. If there a formula I can use to to this? I can only find ones that do one or the other.
    I appreciate your help!

    Ex: Some rows are green, some are yellow and others are orange. Green represents a kind of home and yellow a different and orange a third kind of home. The yellow and orange ones are only color specific and I have a cell at the top that needs to add up the number of yellow and orange rows I have and give a number. The green will change based on a column that will describe if this home is open to build or already being built. I need a formula that will only find the green lines with this specific text in the column and add those up and give a number value in the cell of how many green cells there are with this text. (I hope that helps)

  69. Jyothi Prakash says:

    Hello

    I have cells having letters or numbers and i want to add those cells if they have number and if the cell has letter should be discarded. How to do this?

  70. Francis says:

    Hi there,

    I have a question on sumif and sumifs function. I have a list of data showing contracts names that took place over 2012-2014 for a specific client. I want to set up a summary tab where there are drop downs for contract name and years. so if I were to select 2014 and it'll give me a total figure of how much was made in that year. If I were to select specific contract name for 2014, it'll give me how much was made on that contract in 2014. Also if I was to select all years and all contracts, it'll give me an overall total.

    Would anyone happen to know how I would go about doing that please??

    thanks

  71. Rohit Saluja says:

    Hi,
    Is it possible to add a mathematical condition in Sumif formula. Please refer below example

    A
    1
    2
    3
    4
    5
    6
    7

    Can I add a condition in Sumif such that add only those numbers which are divisible by 3.

    • Pablo says:

      One option is to insert a new column to calculate which values are divisible by 3 and use it as the criteria range for the SUMIFS formula.
      =MOD(cell,3) equal to zero means that the cell value is divisible by 3.
      Use the new column as the criteria range and 0 as the criteria in the SUMIFS formula to add the cells that are divisible by 3. Hope this helps!

  72. Ashley says:

    Hi,

    I am trying to autosum a total column in an invoice table spreadsheet in which it includes the data and the price visited per site. I have written a formula in the date column like such: =IF([@Date]"",167, 0). Hence if there is a date entered it will auto populate 167 in the Price column in the table, if it does not have a date inserted it just '$ - ". But when I try to SUM or IFSUM (=SUMIF([Date], "", [Price]) formulas it rather leaves a "$ - "or has the #### error. I have tried SUMPRODUCT, SUM, SUMIF, and SUMIFS formulas but cannot get it to total sum the columns that have a price compared to not having anything. Please help!

  73. Steve says:

    I am trying to use the sumif function to sum a series of job #'s that are specific to salespeople. I am exporting this list out of our accounting software & I noticed that the job #'s are actually labeled as "text" in my worksheet. What would my criteria be for the following:

    Job Billed to Date

    2074-14 4,306.11
    2999-17 0.00
    4000-17 0.00
    4001-17 0.00
    4002-17 0.00
    4003-17 0.00
    4004-17 0.00
    4005-17 0.00
    4006-17 0.00
    4070-16 18,462.00
    4076-16 10,133.00
    4082-16 7,940.00
    4083-16 7,610.00
    4091-16 7,895.00
    4092-16 5,925.00
    4093-16 7,510.00
    4094-16 0.00
    4095-16 15,863.00
    4096-16 7,876.00
    4097-16 7,200.00
    4098-16 18,360.00
    4101-16 5,450.00
    4102-16 7,500.00
    4103-16 12,100.00
    4104-16 0.00
    4105-15 9,510.00
    4105-16 0.00
    4106-16 2,950.00
    4107-16 7,754.00
    4108-16 0.00
    4999-17 0.00
    5000-16 53,529.00
    5004-16 22,683.00
    5005-16 1,419,164.00
    5007-16 30,299.00
    5008-16 0.00
    5011-16 0.00
    5013-16 0.00
    5014-16 0.00
    5015-15 111,403.52
    5015-16 0.00
    5016-16 0.00
    5017-16 0.00
    5018-16 0.00
    5024-15 57,166.00
    5999-17 0.00

    Column "A" is the job#, column "B" is the billed amount. I am trying to sum the sales for column "A", which each range represents a salesperson. Ex., series 2000-2999 jobs = Bob, series 4000-4999 = Joe & series 5000-5999 = Marie.

    Here is the formula that I am trying to use, but it is not working. Any ideas? Thanks

    =SUMIF('Sep 16'!A2:A1048576,">2000,<2999-99",'Sep 16'!B2:B1048576)

    • tohan says:

      You could do:
      =SUMIF('Sep 16'!A2:A1048576,"2*",'Sep 16'!B2:B1048576) to sum up all job# starting with 2 for instance (repeat accordingly for 3, 4 and 5s or build up a formula like so: C2&"*" for your criteria)

  74. AKASH says:

    hi, just suppose we have 2 list. in first we have customers account and pending amount. in second list of some customers with account who have paid partial of full amount. then how to find net amount? if list are large and we cant sort them..

  75. tohan says:

    Great article! I thought I knew SUMIF but I learnt a lot of things there.

    Similarly to sum-range indicating only the upperleft-most cell, it would seem that range actually indicates only the upperright-most cell is that correct ?

    I stumbled upon a spreadsheet that I couldn't understand how, was giving the correct result, basically instead of =SUMIF($B$2:$B$10,$H$2:$H$10,$D$2:$D:$10) it read =SUMIF($A$2:$B$10,$H$2:$H$10,$C$2:$C:$10)
    I now understand the shift to column C in 'sum_range' as 'range' was 2 columns wide, but I wanted to confirm the behavior of 'range'.
    Thanks!

  76. Darren Haste says:

    Hi, Great blog and I'm hoping you can help?

    I have a large sheet called 'Errors' (11 columns) and in Column A I have employee names who have recorded till errors over 2016, so their names appear multiple times down the list and the value of the error short/over appears in Column J.

    In a separate tab I have the full list of employee names, so for example A1 is Adam. In B1 I'm trying to create a formula that will return all shortages created by Adam in 2016 by looking at the other sheet (A1:K4000. I've been trying something like =VLOOKUP(A1,ErrorsA1:K4000,10,"<0) but it returns a #value error.

    Any help would be greatly appreciated.

    Darren

  77. DallasJewel says:

    This blog is phenomenal. I am stumped. I have an account number for my customers is column A and Column B lists the sale amount. I would like to have column c aggregate the sales for each customer. Some customers have 1 sale each month some have 10. How do I get a running total for customers in column c

  78. Sha says:

    What a great Blog!

    Im not great at excel but your explanations are so simple and easy to understand!!!

    I do have a question though :-p I am currently using you're SUMIF formula for specific text.

    =SUMIF('Main Tracker'!G7:G30,"*GBCDF*",'Main Tracker'!T7:T30)

    I was wondering can you also add a date range to that formula so that it totals up everything with "GBCDF" within a certain date range.

  79. Ccil says:

    Hello,

    Here is my questions with the example

    I have in F2 = "Apples/Bananas"
    I need my formula to be =sumif(A2:A8,F2,C2:C8)

    In F2 I can have whatever sign or space that make this formula works.
    I need this formula to add Apples and Bananas.

    Any thought or help ?

    Thank you in advance
    C.

  80. Jimmy says:

    I can't seem to get this to work. Is this formula legal or possible?

    =SUMIF('ITSC-0101S-01:ITSC-0909S-01'!E3, B3, 'ITSC-0101S-01:ITSC-0909S-01'!B3)

    What is supposed to do is look at cell E3 across multiple sheets and IF it matches cell B3 on the formula sheet then add the corresponding cell B3s together.

  81. Mohamed Azhar says:

    Dear
    Greetings..!

    I'm trying to calculate SUMIF function but i do not know how to calculate and getting my answer but i would expecting you guys will solve my problem in excel.

    i have an excel sheet that contain in Column (A) Date as (2-12-2016 9:37 PM) in Column (B) Names Like (Adam, John, Wiki) in Column (C) an Amount such as 100, 200, 500 in Column (D) Should be My Answer that's are follows

    if in Column (A) Date "1-1-2016 to 31-1-2016" and in Column (B) "Adam" then Calculate the amount in Column (C) "100" and show the Addition in Column (D)

    i tried this but i'm fail

    =SUMIFS(C1:C3,B1:B3,"Adam")+SUMIFS(C1:C3,B1:B3,">=1-01-2016",A1:A3,"<=31-01-2016")

    Kindly Help me
    Thanks in Advance

  82. Rea Sasseville says:

    I want to do a monthly ytd total on a spreadsheet with the months in columns. I have a ytd column and am trying to put in the sumif forumla taking the months Jan - Dec and giving them a value of 1 - 12. I then, in the cell immediately above my formula, have the current month's value. My sumif function is =Sumif(h5:s5,"<=v8",h9:s9) my calculation would be then that it should add all of the columns numbers 1 - 11 as that is the number displaying in v8 and not add the number in the 12 column. However it is adding it. What am I missing?

  83. Iwan says:

    Hi,

    Thanks for this wonderful step-by-step teaching about Excel. I find it very useful.

    I have a problem regarding SUMIF issue. Let me explain.

    =SUMIF(Inc!$M$1:$M$1000, ">=1/2/16", Inc!$J$1:$J$1000)- SUMIF(Inc!$M$1:$M955, ">=7/2/16", Inc!$J$1:$J$1000)

    How can I change ">=1/2/16" and ">=7/2/16" to a cell number like B2 or C3 that contains the particular date like 1/2/16. So, I have made 2 cells that have 1/2/16, and the other one is 7/2/16. Instead of having to input the date manually in the formula, I want to input the cell number that already has the date information in it so I can just copy it down.

    Thanks

    Iwan

  84. Fazal says:

    Hi how can i calculate sum of range if the range has to be given by user.

    If i give 5 in a cell and i need sum of range from a1 to a5 in my result cell B2

    And if i give 7 result should be sum of a1 to a7

  85. NAZAR says:

    IN A FROM 1-31 IS DATE N IN B C D ... IS THE NAME OF THE PERSON
    IF B IS PRESENT IN DAY 1 I HAVE TYPE Y AND IF NOT THEN I HAVE TYPE N. NOW I WANTS TO SUM THE HOW MANY DAYS B OR C OR D WAS ABSENT OR PRESENT HOW CAN I GET THE RESULT IN LAST CELL/COLUMN NO IN 32 NO CELL

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