Excel sumif
Let's construct a SUMIF formula with three arguments to take the SUM of everything in the Baked Goods category: = SUMIF( C3:C7, "="& G4, D3:D7)Īs you can see, the SUMIF function takes the sum of everything our our sum_range ( D3:D7), but only if the corresponding cells in our range ( C3:C7) meet our stated condition ( "="&G4). To see how this works, let's take a look at the spreadsheet from the beginning of this tutorial, which lists product sales by item and category: In other words, SUMIF will take the SUM of everything in sum_range, but only if the given condition is met for everything within the range provided.
This argument will allow us to take the sum of a range that's different from our condition. That's when the third argument of SUMIF comes in: sum_range.
Why would we only want to take the sum of Customers if the number of customers in any given month is greater than seven? What we've done so far is nice, but not particularly useful. See how we use the & sign to join the ">" string with a cell reference to F5? If we included the argument F5 within the quotes, the formula wouldn't work, because Excel would treat it as a string rather than a cell reference. For example, take a look at the following formula, where we've included a cell reference rather than a hard-coded number in the condition: = SUMIF( C3:C6, ">"& F5) However, we find that it's easier to always use the notation containing &, as it'll make things simpler when we include cell references in our conditionals. Instead of writing the condition as ">"&7, we could also write it as ">7", including the number 7 as a part of the string we provide to the function. We then use the ampersand ( &) sign to join this string to a regular number, 7, which we also want to include in our conditional statement. It's a bit confusing for beginners, but that's just the way it's built! We enclose the > sign in quotes ( " ") to turn it into a string that Excel recognizes. What's with the quotes and ampersand around the ">"&7? It turns out that the condition argument takes a string, not a logical expression. Therefore, the only cells it includes are C5 and C6 - which contain the values 8 and 19. This means that Excel should only include cells whose value is greater than or equal to 7 in our SUMIF. As it turns out, the criteria we've set here is ">"&7. However, there's a condition: Excel looks at the criteria column to see whether there are any cells that we should exclude from the SUM. What's going on here? First, Excel looks at the cells C3:C6, which it knows we want to take the SUM of. For example, consider the following formula, which sums up customer numbers by month: = SUMIF( C3:C6, ">"& 7) It takes the sum of every number in the given range, provided that the given criteria for that number is met. With just two arguments, the SUMIF argument is very simple. We'll use that argument with most of our applications of SUMIF, but first, let's take a look at what happens when we leave it off. Notice that the sum_range argument at the end of the function is optional. The formula for the SUMIF function is as follows: = SUMIF( range, criteria, sum_range (optional)) Fortunately, we have the SUMIF function to assist us. With a large spreadsheet, it would be a pain to sum these all up manually. Let's say we want to find out how much money SnackWorld made from selling all items in the Baked Goods category. Take a look at the following table, which shows SnackWorld sales for various types of item, including Brownies, Cookies, and Gummy Worms: We can take the SUM of all products in the category Candy by using a SUMIF function. For example, say we have a database of sales by product category. To begin, we'll start by learning about the most simple of these: the SUMIF function.Įxcel's SUMIF allows you to perform a SUM of a particular range of data, but only include numbers for which certain conditions are met. But it turns out that there are a whole class of even more powerful functions that allow you to perform conditional calculations on large ranges of data. Many Excel users think that the IF function is the program's most powerful conditional tool. By submitting this information, you agree to Deskbright's privacy policy and terms of service.