This tip was printed from the MalekTips Computer and Technology Help and Tips website at http://malektips.com.

Microsoft Excel 2003 Functions and Formulas - Find a Conditional Sum

Sometimes you may need to use only certain values inside a range in a summation, based on a defined condition. Using Microsoft Excel 2003, this can be calculated with the =sumif function. =sumif can also use another range of cells to determine whether or not the condition is met.

Syntax:

=sumif(SERIES,CRITERION,SERIES_OF_NUMBERS)

SERIES is the range of cells you want to evaluate.

CRITERION is the criterion you want to meet to evaluate the number.

SERIES_OF_NUMBERS are the numbers you want to actually sum. This may the same as SERIES, or it may be different.

For example, given the following table of salespeople and their number of sales:


    A       B
1   Tom     1500
2   Rick    2250
3   Jane    2320
4   Tom     1670
5   Jane    1400
6   Jane    2780
7   Rick    1220

To find Jane's total number of sales, use this function:

=sumif(A1:A7,"Jane",B1:B7)

This produces the result 6500 (2320+1400+2780).

To find the sum of the sales that exceed 2000, use this function:

=sumif(B1:B7,>2000)

This produces the result 7350 (2250+2320+2780).

Press the "print" button on your browser or select "File" - "Print" to print this tip. Then, return to Microsoft Excel 2003 Functions and Formulas - Find a Conditional Sum

Standard disclaimer applies - read http://malektips.com/disclaim.html.