Summarize Specific Data using criteria in the SUMIF function

Provided by: FMS Development Team

The Excel SUMIF function allows the user a way to summarize data from one column by using criteria from a different column. The syntax and an example are as follows:

=SUMIF([Criteria Cell Range], [Criteria Value], [Number Range])

=SUM(A1:A250, “OfficeSupplies”, B1:B250)

For an example, suppose that the column A contains categories, and column B contains a numerical amount, and that a summary is needed per category. Assume that data starts on Row 1 and continues to Row 50.

   A             B

---           ---

Cat1       50

Cat2       75

Cat3       88

Cat2       44

Cat3       99

…             …

To summarize only the Category “Cat1”, add the following formula to any cell, (besides where the data is):

                =SUMIF(A1:A50, “Cat1”, B1:B50)

The same can be done for the other categories  by substituting the middle parameter with a different category code.  Visit the Help file to learn about similar functions like SUMIF, COUNTIF, and AVERAGEIF.

Return to the tips page