Microsoft Excel 2007: Countif and Sumif (Functions) (Win)

Countif and Sumif (Functions)

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.

• Countif
• Sumif

Countif

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 (e.g., test scores), or the number of entries that equal a certain value (e.g., 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 Formulas tab, in the Function Library group, click INSERT FUNCTION OROn the Formula Bar, click INSERT FUNCTIONThe Insert Function dialog box appears.
4. From the Or select a category pull-down list, select All or Statistical
5. From the Select a function scroll box, select COUNTIF
6. Click OK
   The Function Arguments dialog box appears.
   
7. In the Range text box, type the range of cells
   OR
   1. Click COLLAPSE DIALOG
   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 DIALOG
8. In the Criteria text box, type the appropriate criteria, such as the numerical range, expression (e.g., >2), or text value
   EXAMPLE: To count the number of students with a class rank of "senior," type senior
9. Click OKThe formula appears in the Formula Bar and the result of the function appears in the cell you selected.
   EXAMPLE: The formula should look something like =COUNTIF(C2:C11,"senior") with C2:C11 being the range, and "senior" the criteria.

Sumif

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 Formulas tab, in the Function Library group, click INSERT FUNCTION OROn the Formula Bar, click INSERT FUNCTIONThe Insert Function dialog box appears.
4. From the Or select a category pull-down list, select All or Math & Trig
5. From the Select a function scroll box, select SUMIF
6. Click OK
   The Function Arguments dialog box appears.
   
7. In the Range text box, type the range of cells to analyze
   OR
   1. Click COLLAPSE DIALOG
   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 DIALOG
8. In the Criteria text box, type the appropriate criteria, such as the numerical range, expression (e.g., >2), or text value
   EXAMPLE: If you would like to add the test scores of the students with a class rank of "senior," type senior
9. In the Sum_range text box, type the range of cells
   NOTE: These are the cells which will be summed if the criteria has been met. If left blank, the cells specified in the Range will be added.
   OR
   1. Click COLLAPSE DIALOG
   2. Within your worksheet, select the range of cells to analyze
   3. Click RESTORE DIALOG
10. Click OKThe formula appears in the Formula Bar and the result of the function appears in the cell you selected.
    EXAMPLE: The formula should look something like =SUMIF(C2:C11,"senior") with C2:C11 being the range and "senior" being the criteria.

 

 

Make sure to let me know in the comments below or on our

Facebook page how you've got with it or Do you have any questions