Some Answers


Searching for Text Fields in Cells

View Instructions for specific text search on youtube

MY videos are all created on Camtasia.
TechSmith Camtasia Click Here

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

Video Editing Made Easy


Sumif and Sumifs

Video on Sumif and Sumifs

MY videos are all created on Camtasia.
TechSmith Camtasia Click Here

  • 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