Blog

Useful Functions in Excel

Addition in Excel

Choose the cell where you want the result to appear as cell C6 in this example. Then type = sign in the cell, now select first cell C3 then type “+” sign then select second cell C4, again type + and select third cell C5. Now press the enter key, you will get the sum of these three numbers.

The red arrow is showing the result of addition

Using Sigma Button to do addition in Excel

Select the cell to display the result then in Home tab click on the Sigma button, on the right end of the ribbon.

Now select the list of data you want to add. You will see the image shown below, and then press the Enter key to get the result.

Calculate average in Excel

Select the cell to display the result e.g. cell C9 in the image shown below. Now type “=” sign in the cell and type “average” then type opening parenthesis. Now select the data, cells from C4 to C7 then press the Enter key to get the result.

Using Sigma Button to calculate average in Excel

Select the cell to display the result. In the Home tab click on the small arrow next to the Sigma button and left click on the Average option. You will see the image shown below. Then press the Enter key to get the result.

Excel If Function

The IF function or IF statement in Excel is composed of three parts separated by commas.

  • A condition
  • What to display if the condition is fulfilled
  • What to display if the condition is not fulfilled
  • Select the cell in which you want to create the “IF function”
  • Type the code in the cell: =if( Type the condition with comma: B4>70,
  • Type what you want to show if condition is fulfilled. If you want to display text then write it within quotation marks: “Unsafe”
  • Type a comma: ,
  • Type what you want to show if the condition is not fulfilled within quotation marks: “Safe”
  • Then close the bracket and press the Enter key.
  • The IF function created above will look like this: =if(B4>70,”Unsafe”,”Safe”)

So the IF function says if the speed or value in the cell B4 is more than 70 then display Unsafe and if it is less than 70 then display Safe. After pressing the Enter key you will get the result for cell B4. Drag the fill handle downward till cell C9 to get status for other speeds of column B.

Excel If Function with Calculations

“IF function” can be used in complex calculations.

For Example: If a sales executive sells more than 5 items, the company will pay incentive 40 rupees per item sold and if the sales executive sells less than 5 items, the company will pay 20 rupees per item sold.

The IF function will look like this: =if(C4>5,40*C4,20*C4)

So the IF function says if value in cell C4 is greater than 5 then multiply it with 40. If it is less than 5 then multiply it with 20.

Excel COUNT

The COUNT function returns the absolute number of cells that include numbers. This count contains both Numbers and Dates.

Where the parameters, value1, [value2], etc. can be any values or authority to cell ranges.

Returns the number of integer values in a given range

Count integer Values in the range A1: A4 and B1:B2

EXCEL COUNTIF

Excel COUNTIF function is utilized for counting cells inside a selected range that meet a specific rule, or condition.

For example, we can write a COUNTIF equation to discover out how many cells in our worksheet include a number higher than or less than the number we consider. Another common use of COUNTIF in Excel is for counting cells with a particular word or beginning with a specific letter(s).

Counts the number of cells that are higher than or equivalent to 5

Excel COUNTIFS

The Excel COUNTIFS function takes in at least one cell range and returns the total counts if criteria are fulfilled.

Where criteria_range1: Arrays of values (or ranges of cells including values) to be examined against the particular criteria1, criteria2 … (The provided criteria_range arrays must all have the similar length). criteria1: The conditions to be examined against the values in the criteria_range1, [criteria_range2],….

Example of the COUNTIFS function

We want to count the number of persons who fulfilled these two criteria.

  • A person should be female.
  • A grade should be <=D, which is between A to C.

Excel sumif Function

The SUMIF function is used to sum up the values of some specific cells of a column, the cells which meet certain criteria. The syntax of SUMIF function is:

So, the SUMIF function has three main parts:

  • Range: It refers to range of cells that you want to evaluate to shortlist the cells that meet the given criteria.
  • Criteria: It refers to conditions that tell which cells are to be added. It can be a number or a text.
  • Sum _range: It provides the actual cells that are to be added. It is an optional argument. If we omit this part of the function the SUMIF function treats “range” as “sum_range” thus adds the cells of the range argument.

Example:

The company wants to know the total salary of the marketing department. In this case, by applying the SUMIF function the company can add the salaries of all employees of marketing department.

  • =sumif(C3:C12,”marketing”,D3:D12)
  • Range: C3:C12 is the range to be evaluated to shortlist the cells that belong to marketing department.
  • Criteria: “Marketing” is the criterion to evaluate the range or to find out the cells that are to be added.
  • Sum_range: D3:D12 is the sum_range that provides the actual values to be added based on the given criterion.

Leave a Reply

Your email address will not be published. Required fields are marked *