This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

Functions: Countif and Sumif

Countif and Sumif are two functions which are helpful and very easy to use. Countif counts the number of items that meet a certain criteria. Sumif adds all items that meet a certain criteria.

return to topCountif 

The Countif function counts the number of items that meet a certain criteria. This criteria can be a number range or a word. This is useful when determining the number of entries within a certain range, such as test scores or the number of entries that equal a certain value, like the class rank of "junior" or "senior." 

  1. Open the desired worksheet

  2. Select the cell where you want the result of the function to appear

  3. From the Insert menu, select Function...
    OR
    Windows: On the Formula Bar, click INSERT FUNCTIONInsert Function button
    The Insert Function dialog box appears.
    Macintosh: On the Standard toolbar, click PASTE FUNCTIONPaste Function button
    The Paste Function dialog box appears.

  4. Windows: From the Or select a category pull-down list, select All or Statistical
    Macintosh: From the Function category scroll box, select All or Statistical

  5. Windows: From the Select a function scroll box, select COUNTIF
    Macintosh: From the Function name scroll box, select COUNTIF

  6. Click OK
    The Function Arguments dialog box appears.
    Function Arguments dialog box

  7. In the Range text box, type the range of cells
    OR
    1. Click COLLAPSE DIALOGCollapse Dialog box buttonorCollapse Dialog box button
    2. Within your worksheet, select the range of cells to analyze
      NOTE: These are the cells which will be analyzed to see if they meet the criteria you select. 
    3. Click RESTORE DIALOGRestore Dialog box buttonorExpand Dialog box button

  8. In the Criteria text box, type the appropriate criteria, such as the numerical range, text value, or expression (e.g., >2)
    EXAMPLE: To count the number of students with a class rank of "senior," type senior

  9. Click OK
    The appropriate number appears in the cell you selected and the formula appears in the Formula Bar.
    EXAMPLE: The formula should look something like =COUNTIF(C2:C11,"senior") with C2:C11 being the range, and "senior" the criteria.

return to topSumif

The Sumif function adds all items that meet a certain criteria. For example, you could add all the scores of students with a class rank of "senior."

  1. Open the desired worksheet

  2. Select the cell where you want the result of the function to appear

  3. From the Insert menu, select Function...
    OR
    Windows: On the Formula Bar, click INSERT FUNCTIONInsert Function button
    The Insert Function dialog box appears.
    Macintosh: On the Standard toolbar, click PASTE FUNCTIONPaste  Function button
    The Paste Function dialog box appears.

  4. Windows: From the Or select a category pull-down list, select All or Math & Trig
    Macintosh: From the Function category scroll box, select All or Math & Trig

  5. Windows: From the Select a function scroll box, select SUMIF
    Macintosh: From the Function name scroll box, select SUMIF

  6. Click OK
    The Function Arguments dialog box appears.
    Function Arguments dialog box

  7. In the Range text box, type the range of cells
    OR
    1. Click COLLAPSE DIALOGCollapse Dialog box buttonorCollapse Dialog box button
    2. Within your worksheet, select the range of cells to analyze
      NOTE: These are the cells which will be analyzed to see if they meet the criteria you select. 
    3. Click RESTORE DIALOGRestore Dialog box buttonorExpand Dialog box button

  8. In the Criteria text box, type the appropriate criteria (such as the numerical range or text value)
    EXAMPLE: If you would like to add the test scores of the students with a class rank of "senior," type senior
    OR
    1. Click COLLAPSE DIALOGCollapse Dialog box buttonorCollapse Dialog box button
    2. Within your worksheet, select the appropriate criteria 
    3. Click RESTORE DIALOGRestore Dialog box buttonorExpand Dialog box button

  9. In the Sum_range text box, type the range of cells
    EXAMPLE: If you would like to add the text scores of seniors, select the cell range that contains the test scores
    OR
    1. Click COLLAPSE DIALOGCollapse Dialog box buttonorCollapse Dialog box button
    2. Within your worksheet, select the range of cells to analyze
      NOTE: These are the cells which will be summed of the criteria has been met.
    3. Click RESTORE DIALOGRestore Dialog box buttonorExpand Dialog box button

  10. Click OK
    The sum appears in the selected cell and the formula appears in the Formula Bar.
    EXAMPLE: The formula should look something like =SUMIF(C2:C11,"senior") with C2:C11 being the range and "senior" being the criteria.

Excellence. Our Measure. Our Motto. Our Goal.