## Searching for Text Fields in Cells

MY videos are all created on Camtasia.

We will introduce the search function: this function searches for the position for text within a field.

=Search("the text you want", cell name)

=Search("uofa",F15)

The formula returns 7

• We will now enter text that it will not find.

=search("uofc", F15)

The formula returns the error message #value

• When combining formulas to display the names of several universities from an email, you must use the IFERROR
• The formula for searching for three universities would be

=IFERROR(IF(SEARCH("uofa",F15)>0,"University of Alberta"),IFERROR(IF(SEARCH("uofc",F15)>0,"Calgary University"),IFERROR(IF(SEARCH("au.ca",F15)>0,"Athabasca University"),"")))

• The correct University indicated from the email address will be displayed

## Sumif and Sumifs

MY videos are all created on Camtasia.

• Sumif is a very handy tool to total a variety of data by criteria.
• Similar to "if statements" you set a criteria.
• We have student marks, using an "if statement we have catgorized the results.
• We would like to total the course fee by results
• We will total the course fees for those who failed the course
We will use the funtion dialog box by clicking on the

# fx

and typing in sumif in the search for funtion box
• The Range is the cells that you are testing the criteria
• Criteria is what you are test for, in our case "fail"
• Sum_range is the cells that contain the values that you want to total
• From the Funtion Argument box you can see the result is 6000
• Now, we would like to total the fees for student for failed but are able to do a w rewrite.
We will use the funtion dialog box by clicking on the

# fx

and typing in sumifs in the search for funtion box
• The dialog box has a different order, the first is the Sum_range
• Sum_range is the cells that contain the values that you want to total
• Next; Criteria_range1, when the criteria range is select, you will be requird to enter a criteria for that range.
• You will be give to select a another criteria range and criteria
• Sum_range will be the fee values
• The first critera, will be pass or fail we will select "fail"
• The second criteria will be "rewrite"
• From the Funtion Argument box you can see the result is 3750

## Sign-up for our newsletter and receive a free calendar pop-up for entering dates

* indicates required