Top 4 Basic Formulas You Should Know (Excel 2010)

There are just so many formulas in Excel that you could use to your advantage, especially when your report involves a fair bit of calculation or requires a certain amount of analysis. I will share with you, my top four basic formulas that I frequently use in most of my reporting tasks.

1.  SUM

This has got to be the most basic formula that the entire IT population have probably used. SUM lets you add up at least two numbers together. You can use SUM in any of the following scenarios:

a)     Add numbers in a column or a row

Formula: =SUM(CellNumber1:CellNumber2)

Example: You would like to add up all the numbers from Project A to E under the Revenue column. In cell C9, type the formula =SUM(C4:C8) and hit the Enter button. The result is 53,853.27

basic_formula_1

Note: You can also use the AutoSum function to add up the numbers. Simply select cell C9, click the AutoSum button and press Enter.

basic_formula_2

 

b)     Add numbers from selected cells

Formula: =SUM(CellNumber1+ CellNumber2+ CellNumber3)

Example: You would like to add up the numbers for Vol 2, Vol 4 and Vol 6 under the Used column. In cell C10, type the formula =SUM(D4+D6+D8) and hit the Enter button. The result is 140.40.

basic_formula_3

Note: You can also exclude the word SUM and type =D4+D6+D8 directly into the cell and hit the Enter button.

 

c)     Add numbers in a cell (acts like your calculator)

Formula:  =SUM(number1 + number2 + number3)

Example: In cell B2, type the formula =SUM(1+5+9) and hit the Enter button. The result is 15.

basic_formula_4

Note: You can also exclude the word SUM and type =1+5+9 directly into the cell and hit the Enter button.

 

2.  SUMIF

Using the SUM formula is as easy as 1-2-3. What if you need to add up numbers based on certain condition? Well, that’s entirely possible by using the SUMIF formula. Let’s explore the following examples:

a)     Add up the total revenue earned by a particular Project Manager for projects that he manage

Formula: =SUMIF(cell range to look for, criteria/condition to meet, cell range to add up when criteria/condition is met)

Example: I want to know the total revenue earned by a Project Manager named William Windsor. So, the cell range that I should look for his name is column C (i.e. from cell C4 to C10), the criteria is “William Windsor” and the cell range to add up the revenue (when the criteria is met) is column D (i.e. from cell D4 to D10).

In cell D11, type the formula =SUMIF(C4:C10,”William Windsor”, D4:D10) and hit the Enter button. The result is 206,100.

basic_formula_5

 

b)     Add up the total used size in a disk volume where the migration status for the disk is completed

Formula: =SUMIF(cell range to look for, criteria/condition to meet, cell range to add up when criteria/condition is met)

Example: I want to know the total used size in a disk volume where the data migration for that particular disk has been performed. So, the cell range that I should look for the migration status is column E (i.e. from cell E7 to E14), the criteria is “Completed” and the cell range to add up the used disk volume (when the criteria is met) is column C (i.e. from cell C7 to C14).

In cell B3, type the formula =SUMIF(E7:E14, “Completed”,C7:C14) and hit the Enter button. The result is 553.42.

basic_formula_6

basic_formula_7

 

3.  COUNTA

This formula counts the number of cells that contain data (whether the data is a number, text or combination of both) within the defined range. And yes, it ignores blank cells. See the following example:

Formula: =COUNTA(value1, value2, …) where value refers to the type of argument used, such as cell references, range references or multiple rows/columns.

Example: I want to know the number of projects that I have completed uploading the documentation into our document repository. So, for this example, I will be using the range reference argument type (i.e. cell B4 to B13).

In cell B14, type the formula =COUNTA(B4:B13) and hit the Enter button. The result is 10. Note that the Project ID column contains a combination of number and alphabet.

basic_formula_8

basic_formula_9

4.  COUNTIF

Now, how about counting something within a range that is based on a certain condition? No problemo! The example below shows you how:

Formula: =COUNTIF(cell range to look for, criteria/condition to meet)

Example: I want to know the number of reserved ports in a SAN switch where the WWN Address column contains the word “Reserved”. So, the cell range that I should look for is column C (i.e. from cell C3 to C10) and the criteria is “Reserved”.

In cell C12, type the formula =COUNTIF(C3:C10,”Reserved”) and hit the Enter button. The result is 3.

basic_formula_10

basic_formula_11

2 Responses to Top 4 Basic Formulas You Should Know (Excel 2010)

  1. Mohan Menon says:

    Way to go! thanks for the info! from Biker B.

Leave a Reply to Mohan Menon Cancel reply

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