Excel formula: SUM vs SUMIF

Office 365 / SharePoint Blog

Excel is a useful tool any time you want to analyze or manipulate data. I use it regularly and thought I would create a few posts about its usefulness.

Excel uses formulae to perform its calculations. You begin a formula with the equal sign (=) and then you can tell Excel to do a bunch of things, one of which is add numbers in a row or column.

In order to do that, you use the SUM formula.

The SUM formula is generally used on a range of cells.

For example the formula =SUM(A1:A5) will add the first five numbers column A.

If you had the numbers 10, 20, -15, 25, and 30 in the first column, using =SUM(A1:A5) would generate 60 as your total.

Now let’s imagine that you only want the positive numbers to be added. You can use the SUMIF formula to accomplish that. Taking the same numbers above: 10, 20, -15, 25, and 30, using =SUMIF(A1:A5,>0) would generate 85 as your total.

There is a third piece to the SUMIF function that lets you compare non-numerical information, and then add the numbers that match. For example if you had a list of T-shirts in different colors and sizes, and you wanted to grab your totals by size or by color, you would use SUMIF.

Excel: SUMIF function

To give an example, the formula in F4 would read:


So let’s take the parts of that:

  • The first part is – look at the information in column A, which is the color column
  • The second part is – compare that information against E4, which is Maroon
  • The third part is – take the numbers from the quantity column to add up the total number of Maroon T-shirts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s