## 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**

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

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**.

**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**.

**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**.

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**.

** **

**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.

**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**.

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

Thank you Sir! :)