Tag: formulas

Excel: SUMIF() and COUNTIF()

On By Ben Larson Ph.D.In excelLeave a comment

SUMIF() and COUNTIF()

Download Practice Excel File Here: Conditional Formula

SUMIF()

SumIf is a handy Excel function to have in your tool box. It is basically a combination of SUM() and IF(). What it is saying is, SUM up all numbers in a range that meet certain condition.

The syntax is as follows:

=SUMIF(range, criteria, [sum_range])

Range = range of numbers you want to test the criteria against

Criteria = the logical test (is the data in the range =, >, < a value)

[sum_range] = this is an optional value. It tells Excel which cells to add. If you don’t use this value, Excel automatic defaults to the first Range

excelSumif

COUNTIF()

CountIF counts the number of cells that meet a certain criteria.

Syntax is similar to SUMIF():

=COUNTIF(range, criteria)

** Note this example uses wildcards – symbols that can be used in place of any symbol. In Excel, the two most popular wildcards are * and ?. ? can be used in place of one character (B?d = Bad, but B?d <> Bead). * can be used in place of many characters. B* = Brain and B*d = Bead)

excelcountif

Excel: =If() – Conditional Formulas

On By Ben Larson Ph.D.In excel2 Comments

=If()

Download Practice Excel File Here: Conditional Formula

Conditional formulas can really up your Excel game. This can provide a lot more functionality to your workbooks.

So what are conditional formulas? Conditional formulas work based on the following logic: IF condition THEN action.

examples: IF gas tank is low THEN  go to gas station

IF age > or = to 21 THEN you can drink

The basic Excel formula for this is =If()

Syntax

=If(logical test,[value if true],[value if false])

Example 1

In the first example, we are going to see if a value in Column A is > 10. If it is, we will return High. If not, it will return Low.

excelIf1

It also works with strings.

Example 2

In this example, we are asking if any name in column D matches Bob, then Yes, else No.

excelIf2

 

 

Excel: Learning to Use Formulas

On By Ben Larson Ph.D.In excelLeave a comment

The first step towards being an Excel Power User is learning to use formulas. Pulling out a calculator to add up Excel columns is a sure fire way to let everyone in the office know your computer skills are lacking. Luckily for you, basic Excel formulas are pretty easy learn.

Lesson 1: Basic Arithmetic Formulas

Video Link: Excel Formulas Part 1

Excel supports all standard Arithmetic functions – addition, subtraction, multiplication, division, etc. **Excel comes standard with advanced engineering and statistical functions, but that is well beyond the scope of this article.

To start a formula in Excel: select the cell where the result belongs and type =. Using = as the first character tells Excel you want to create a formula.

In this example, we want to add the first row of column A and B and place the answer in C. Select cell C2, enter =A2+B2. Press enter and 7 will appear in the space. Try changing the values in cells A2 and B2. Notice the value in C2 changes in kind.

formulas1

Notice how as you type in a formula, it populates in the formula bar above as well. You can work in the formula bar instead if you chose (as your formulas get longer and more complex, you will find yourself doing more work in the formula bar).

You can try out some other arithmetic functions following the example below: **note: make sure to start your formulas with =

formula2

Lesson 2: Replicating Formulas

Video Link: Excel Formulas Part 2

If you had to input each formula individually every time you wanted to use one, this would not be any more efficient then simply doing the calculations on your old desktop calculator. Fortunately, Excel makes replicating your formulas across many cells as simple as clicking a mouse button.

In the bottom right corner of an active cell, there is a small green box. You can click on this box and drag it over the cells you would like the formula to replicate. Or, you can just double click corner and it will automatically replicate the remaining rows in your table.

formula3

Lesson 3: Functions

Video Link: Excel Part 3

Excel has hundreds of built in functions (far too many to cover in this article). These functions save you having to write complex formulas manually.

Below are examples of 3 commonly used functions (SUM, PRODUCT, AVERAGE). formula4

Let us break these functions down:

  1. SUM() — adds the values of all cells listed in the parenthesis.  **In the example above, the SUM function has C2:C5 in the parenthesis. The colon(:) means between. So C2:C5 actually means all the values C2 through and including C5 (C2,C3,C4,C5)
  2. PRODUCT() — multiplies the values of all cells listed in the parenthesis.
  3. AVERAGE() — takes the average of the values of all cells listed in the parenthesis.

If you are curious as to what other built in functions Excel offers, go to the Formulas tab in the ribbon cable. 

formula6