Learn Microsoft Excel

Hi,
In this modern age microsoft excel has become important for every person who is engaged in any type of business. This tool help you to prepare more complex calculation easily with built-in functions. Such as preparing Costing and Budgeting sheets, making leasing schedule, amortization and depreciation schedule, etc. This website will enable you to learn and understand the excel and also you can ask any query relating to excel and we will answer you through your e-mail address.

Tuesday, January 25, 2011


The COUNTIF function, one of Excel's COUNT functions, is used to count up the number of cells in a selected range that meet certain criteria.

The syntax for the COUNTIF function is:

=COUNTIF ( Range, Criteria)

Range - the group of cells the function is to search.

Criteria - determines whether the cell is to be counted or not. This can be a number, expression, cell reference, or text string.

Example Using Excel's COUNTIF Function:

Note: For help with this example, see the image above.

  1. Enter the following data into cells E1 to E6: 114, 165, 178, 143, 130, 165.

  2. Click on cell E7 - the location where the results will be displayed.

  3. Click on the Formulas tab.

  4. Choose More Functions > Statistical from the ribbon to open the function drop down list.

  5. Click on COUNTIF in the list to bring up the function's
  6. dialog box.

    In the dialog box, click on the button at the end of the Range line to return to your spreadsheet.


  7. Drag select cells E1 to E6 on the spreadsheet to highlight them.

  8. Click on the button at the end of the Range line to return to the dialog box.

  9. On the Criteria line in the dialog box, type "165".

  10. Click OK.

  11. The answer 2 should appear in cell E7 since two cells in the range - A2 and A6 - contain the number 165 and, therefore, match the criteria argument.

  12. When you click on cell E7 the complete function = COUNTIF (E1 : E6 , 165 ) appears in the formula bar above the worksheet.

Using the IF Function to Enter Data

The basic form or syntax of the function is:

=IF(logic test, value if true, value if false)

The logic test is always a comparison between two values. Comparison operators are used, for example, to see if the first value is greater than or less than the second, or equal to it.

While the logic test section is limited to answering a true or false question, you have greater flexibility in what you place in the last two arguments.

Entering Data with the IF Function

The IF function can be set up to enter data into a target cell. This data could then be used in other calculations. For example, a quiz setup in Excel could use IF functions to determine if the student’s responses to questions are correct.

If they are correct, the function returns a mark for that question. If the student gets the question wrong, a zero is returned in the mark column. At the bottom of the mark column, the sum function can be used to total the student’s score on the quiz.

Examples

A math quiz ask the question “what is 5 X 7 equal to?” the IF function that would check to see if the answer was correct and, if so, give the student a mark would be:

=IF(A5 = 35,1,0)

Similarly, a geography quiz could ask “What is the capital of Australia?” An IF function that would check to see if that answer was correct and give the student 5 marks would be:

=IF(A5 = “Canberra”,5,0)

How the IF Function Works

The Excel IF function adds flexibility to your spreadsheet by introducing decision making. It does this by testing a specified condition in your spreadsheet to see if it is true or false.

If the condition is true, the function will carry out one action. If the condition is false, it will carry out a different action.

The function allows you to specify what actions it should carry out depending on whether the condition is true or not. These actions can include executing a formula, inserting a text statement, or leaving the target cell blank.











This tutorial uses the IF function to calculate a fictitious annual deduction for employees based on their yearly salary.

The function tests to see if an employee's salary is above or below the threshold of $30,000. If the salary of employee exceed $30,000 then 6% will be deducted otherwise 8% will be deducted.