Skip to content

Popular Functions in Excel: Lesson 5 – “SUMIF”

2 minute read

Excel Green1

In part five of our miniseries on the most popular Excel functions, we’ll cover the “SUMIF” function, which adds values in a range that meet a specified criteria.

If you missed the first parts of our series, check out part one (“IF”)two (“SUM”), three (“COUNTIF”), and four (“VLOOKUP”).

The SUMIF function offers the functionality of the SUM command but allows you to combine it with conditional terms–i.e., only IF your specific criteria is met will Excel go ahead and execute the sum.

Let’s take a look at the various parts of the command and how it works.

  • First, we’ll enter =SUMIF(.
  • Then we need to enter a range, the criteria for the IF statement and then the sum range. 
  • So what is the first range item for? This is the range of items that must meet the criteria that we’re going to set up. 
  • So let’s say we want this entire block of cells to meet a criteria that we’ll establish, and we want numbers included only if a certain conclusion is reached here.



  • Since in our first range we have mixture of positive and negative numbers, let’s say we want our criteria to be any numbers greater than zero. So we’ll enter “>0” followed by a comma. 



  • Next, sum range is optional. These are the cells to add if we want to add cells other than those specified in the range section.



  • When we hit Enter, we get our answer. Wherever we meet the criteria, we’re adding those values into our sum. It will only draw data from the places we specified based on the criteria, allowing us greater control over the sums we’re returning into our spreadsheet.



  • You can specify various types of criteria. For instance, if we have values that correspond with certain years, we could set a SUMIF function to go through and only add values that correspond to the year 2010, like this:



Click here to watch this video on YouTube.

Sign up for our newsletter