Using “SUMIF” in Excel

Let’s talk about the SUMIF command in Microsoft Excel

Here is some sample data we are working with in this example…

Rows 2 through 11 have the details of a spreadsheet showing several invoices and the sales person for each invoice. If we want to get the total sales by person we use the SUMIF function that range of data.

Cell B14 we would enter…
=SUMIF(C3:C10,”JANE”,B3:B10)
This tells Excel to look in cells C3 to C10 and if the in those cells are JANE then total the values in column B3 . In this instance it returns $1,450 (250 + 1,200).

The formula in cell B15 is

=SUMIF(C3:C10,”PHIL”,B3:B10)

and it returns a value of $1,450 (650 + 700 +100).

The formula in cell B15 is :

=SUMIF(C3:C10,”KEN”,B3:B10) and if returns a value of $1,600 (650 + 670 + 280).

If we wanted to sum invoices that were over $500 we could enter…
=SUMIF(B3:B10,”>500″)
Which would return $3,870 (650 + 700 + 650 + 1,200 + 670).

Or if we only wanted to add numbers under $500 we would enter…
=SUMIF(B3:B10,”<500″)
Which would return $630 (250 + 100 + 280).


In Excel you can also give ranges names and use the name in formulas.

Let’s name a cell B11″Sales”…
Excel select the “Formulas” tab and the select “Name Manager” and then “New Name” and then I will typed Sales into the box that popped up…

Now I can enter =sales in any cell and it will pull in the total sales number of $4,500.

An Excel Tip…
If you enter this into a cell..
=”Total Sales were $”&B11

The cell will show:
Total Sales were $4500

In Excel you can combine cells and/of text by using “&”.
This was just a simple example to show you the using SUMIF in Excel can make your life easier. I’ve seen many coworkers manually adding together cells when a simple SUMIF formula would quickly get the answer they need.