EXCELlent Ones Consulting Skip to Content Skip to Search

Print  |   | Download PDF

Sumif

The SumIf formula can be used for a variety of purposes. Basically this formula sums a set of values if it meets a certain criteria. In the example below we have a sales data for several units in various cities on the left hand side. Let’s say the manager would like to rollup the data by city. We would use the SumIF formula to do this.

1. Type in the cities that occur in the sample data.

a. Note: The text must match exactly to the text in the data. Spaces or inconsistent spelling will throw off the computations.

2. Formula =Sumif(Range, Criteria, Sum Range)

a. Range: This represents the array or list which contains the data you would like to group by. Type in “B2:B23

b. Criteria: This represents the exact description which you are searching for. In this case “New York City”. Type in “E2

c. Range: This range references the range which contains the values to be grouped. Type in “C2:C23

3. Now we see a total for the number of sales in New York City. (ex. $251,665)

In order to group by the other cities we need to do two things: add anchors to the ranges and copy the formula down. Adding anchors will prevent the address from getting changed as it is being copied down.

4. To add “anchors” to the range and sum range cell addresses

a. Highlight the cell address and press F4. This will automatically add ($) to the address.

5. Highlight and copy the formula and paste into the into the following rows

6. As a check it’s a good idea to add totals to the summary data and the detailed transaction table. This ensures that the formulas are correct and all areas are being captured.

The SumIf can be used in countless situations to summarize complex and extensive data for quick analysis.


Footer

Email: info@excellentones.com | P.O. Box 160493 | Brooklyn, NY 11216 | Phone: (347) 763-1035 | Fax: (718) 228-7859

All rights reserved. Copyright 2014 Excellent Ones Consulting LLC.