The SUMIF Function
Overview
- I’m a sales manager for a makeup company and have summarized for each sales transaction the following information: salesperson, date of sale, units sold (or returned), total price received (or paid out for returns). How can I answer the following questions?
- What was the total dollar amount of merchandise sold by each salesperson?
- How many units were returned?
- What was the total dollar volume sold in 2005 or later?
- How many units of lip gloss were sold?
- How much revenue did lip gloss sales bring in?
- What dollar amount of sales were not made by Jen?
If you want to sum all the entries in one column (or row) that match criteria-that depend on another column (or row), the SUMIF function gets the job done. The syntax of the SUMIF function is SUMIF(range, criteria, [sum range]).
- Range is the range of cells that you want to evaluate with a criterion.
- Criteria is a number, date, or expression that determines whether a given cell in the sum range is added.
- Sum range is the range of cells that are added. If sum range is omitted, it is assumed to be the same as range.
The rules for criteria you can use with the SUMIF function are identical to the rules used with the COUNTIF function. For information about the COUNTIF function, see post The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions.
What was the total dollar amount of merchandise sold by each salesperson?
Our work for the problems in this chapter is in the file Makeup.xls. The following figure shows a subset of the data.
As usual, we begin by naming the data in columns G through L with the corresponding names in cells G4:L4. For example, the range name Product corresponds to the range J5:J1904. To compute the total amount sold by each salesperson, I simply copy from cell B5 to B6: B13 the formula SUMIF(Name,A5,Dollars). This formula adds up every entry in the Dollars column that has the name Emilee in the Name column. We find that Emilee sold $25,258.87 worth of makeup, as you can see in the following figure.
How many units were returned?
In cell B16, the formula SUMIF(Units,”<0″,Units) adds up every cell in the Units column (column K) that has a number less than 0. The result is -922. After inserting a minus sign in front of the SUMIF formula, we see that 922 units were returned. (Recall that when the sum range argument is omitted from a SUMIF function, Excel assumes that sum range equals range. Therefore, the formula -SUMIF(Units,”<0″) would yield 922 as well.)
What was the total dollar volume sold in 2005 or later?
In cell B17, the formula SUMIF(Date,”>= 1/1/2005″,Dollars) adds up every cell in the Dollar column (column L) that has a date on or after 1/1/2005. We find that $157,854.32 worth of makeup was sold in 2005 or later.
How many units of lip gloss were sold? How much revenue did lip gloss sales bring in?
In cell B18, the formula SUMIF(Product,”lip gloss”,Units) adds up every cell in the Units column that has the text ‘lip gloss’ in the Product column (column J). You can see that 16,333 units of lip gloss were sold. Transactions in which units of lip gloss were returned are counted as negative sales.
In similar fashion, in cell B19 the formula SUMIF(Product,”lip gloss”,Dollars)tells us that $49,834.64 worth of lip gloss was sold. This calculation counts refunds associated with returns as negative revenue.
What dollar amount of sales were not made by Jen?
In cell B20, the formula SUMIF(Name,”<>Jen”,Dollars) sums up the dollar amount of each transaction that does not include Jen in the Name column. We find that saleswomen other than Jen sold $211,786.51 worth of makeup.
Explore posts in the same categories: MS Excel Tune Up